﻿using eCashDataLayer;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace eCashDatalayer
{
    public class eCashDatalayer : IeCashDatalayer
    {
        #region Connection objects
        String strCon = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

        String Strcon = ConfigurationManager.ConnectionStrings["OPCRegistry"].ConnectionString;

        SqlCommand cmdselect;

        SqlCommand cmdDelete;
        SqlConnection con; 
        SqlCommand cmdUpdate;
        SqlCommand cmdinsert;
        SqlDataAdapter da;

        #endregion 

        #region Permission Module

        public void RegisterUser(RegistrationModel RegistrationModel)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_Registeruser";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            cmdselect.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value = RegistrationModel.sFirstName;

            cmdselect.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value = RegistrationModel.sLastname;

            cmdselect.Parameters.Add("@Username", SqlDbType.VarChar).Value = RegistrationModel.sUsername;

            cmdselect.Parameters.Add("@Password", SqlDbType.VarChar).Value = RegistrationModel.sPassword;

            cmdselect.Parameters.Add("@GUIDR", SqlDbType.VarChar).Value = RegistrationModel.sGUIDR;

            cmdselect.Parameters.Add("@Email", SqlDbType.VarChar).Value = RegistrationModel.sEmail;

            cmdselect.Parameters.Add("@Tel", SqlDbType.VarChar).Value = RegistrationModel.sTel;

            cmdselect.Parameters.Add("@Province", SqlDbType.VarChar).Value = RegistrationModel.sProvince;

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }

        public void RegisterUserTuckShop(TuckShopRegistrationModel TuckshopRegistrationModel)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_RegisteruserTuckshop";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            cmdselect.Parameters.Add("@TUCKSHOPNAME", SqlDbType.VarChar).Value = TuckshopRegistrationModel.sTuckShopname;

            cmdselect.Parameters.Add("@SCHOOLNAME", SqlDbType.VarChar).Value = TuckshopRegistrationModel.sSchoolname;

            cmdselect.Parameters.Add("@Username", SqlDbType.VarChar).Value = TuckshopRegistrationModel.sUsername;

            cmdselect.Parameters.Add("@Password", SqlDbType.VarChar).Value = TuckshopRegistrationModel.sPassword;
             
            cmdselect.Parameters.Add("@GUIDR", SqlDbType.VarChar).Value = TuckshopRegistrationModel.sGUIDR;

            cmdselect.Parameters.Add("@Email", SqlDbType.VarChar).Value = TuckshopRegistrationModel.sEmail;

            cmdselect.Parameters.Add("@Tel", SqlDbType.VarChar).Value = TuckshopRegistrationModel.sTel;

            cmdselect.Parameters.Add("@Province", SqlDbType.VarChar).Value = TuckshopRegistrationModel.sProvince;

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }

        public int CheckUserExistance(String _sUSERNAME)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_CheckUserExistance";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            cmdselect.Parameters.Add("@USERNAME", SqlDbType.VarChar).Value = _sUSERNAME;

            cmdselect.Parameters.Add("@RESULTS", SqlDbType.Int);

            cmdselect.Parameters["@RESULTS"].Direction = ParameterDirection.Output;

            int isValid = 0;

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();

                isValid = (int)cmdselect.Parameters["@RESULTS"].Value;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return isValid;
        }

        public int CheckEmailExistance(String _sEMAIL)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_CheckemailExistance";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            cmdselect.Parameters.Add("@EMAIL", SqlDbType.VarChar).Value = _sEMAIL;

            cmdselect.Parameters.Add("@RESULTS", SqlDbType.Int);

            cmdselect.Parameters["@RESULTS"].Direction = ParameterDirection.Output;

            int isValid = 0;

            try
            { 
                con.Open();
              
                cmdselect.ExecuteNonQuery();

                isValid = (int)cmdselect.Parameters["@RESULTS"].Value;
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return isValid;
        }
        
        public RegistrationModel RecoverCredentials(RegistrationModel RecoverCredentials)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_RecoveruserCredentials";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            if (RecoverCredentials.sEmail != null)
            {
                cmdselect.Parameters.Add("@EMAIL", SqlDbType.VarChar).Value = RecoverCredentials.sEmail;
            }

            if (RecoverCredentials.sUsername != null)
            {
                cmdselect.Parameters.Add("@USERNAME", SqlDbType.VarChar).Value = RecoverCredentials.sUsername;
            }

            RegistrationModel m = new RegistrationModel();

            DataTable dtcredentials = new DataTable();
           
            da.SelectCommand = cmdselect;
            try
            {
                con.Open();

                da.Fill(dtcredentials);

                if (dtcredentials.Rows.Count > 0)
                {
                    for (int i = 0; i < dtcredentials.Rows.Count; i++)
                    {
                        m.sUsername = dtcredentials.Rows[i][0].ToString();
                        m.sPassword = dtcredentials.Rows[i][1].ToString();
                        m.sEmail = dtcredentials.Rows[i][2].ToString(); 
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m;
        }

        private UserModel GetPermissions(String Username)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_GetPagePermission";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            cmdselect.Parameters.Add("@USERNAME", SqlDbType.VarChar).Value = Username;

            UserModel m = new UserModel();

            DataTable dtpermissions = new DataTable();
           
            da.SelectCommand = cmdselect;

            List<string> LstPermissions = new List<string>();

            try
            {
                con.Open();

                da.Fill(dtpermissions);

                if (dtpermissions.Rows.Count > 0)
                {
                    for (int i = 0; i < dtpermissions.Rows.Count; i++)
                    {
                        m.iUserid = Convert.ToInt32(dtpermissions.Rows[i][0]);
                        m.sUsername = dtpermissions.Rows[i][1].ToString();
                        m.sFirstName = dtpermissions.Rows[i][2].ToString();
                        m.sLastname = dtpermissions.Rows[i][3].ToString();
                        m.sEmailAddress = dtpermissions.Rows[i][4].ToString();
                        m.bIsactive = Convert.ToBoolean(dtpermissions.Rows[i][6]);
                        m.bIsconfirmed = Convert.ToBoolean(dtpermissions.Rows[i][7]);
                    }
                    foreach (DataRow row in dtpermissions.Rows)
                    {
                        //get List of Permissions 
                        LstPermissions.Add(row["PERMISSIONNAME"].ToString());
                    }
                }
                m.bIsValid = true; //this will confirm to the BLL Function that this login is Valid 
                m.sPermissions = LstPermissions;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m;
        }

        public int ConfirmRegistration(String _sGUID)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_ConfirmRegistration";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            cmdselect.Parameters.Add("@GUID", SqlDbType.VarChar).Value = _sGUID;

            cmdselect.Parameters.Add("@Valid", SqlDbType.Int);

            cmdselect.Parameters["@Valid"].Direction = ParameterDirection.Output;

            int isValid = 0;

            //if(

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();

                isValid = (int)cmdselect.Parameters["@Valid"].Value;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return isValid;
        }

        public UserModel AuthonticateUser(String _sUsername, String _sPassword)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_Authonticateuser";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            cmdselect.Parameters.Add("@USERNAME", SqlDbType.VarChar).Value = _sUsername;

            cmdselect.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = _sPassword;

            cmdselect.Parameters.Add("@RESULTS", SqlDbType.Int);

            cmdselect.Parameters["@RESULTS"].Direction = ParameterDirection.Output;

            int isValid = 0;

            UserModel m = new UserModel();

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();

                isValid = (int)cmdselect.Parameters["@RESULTS"].Value;

                if (isValid > 0)
                {
                    m = GetPermissions(_sUsername);
                }
                else
                {
                    m.bIsValid = false;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m;
        }

        public void ChangePassword(int UserID, String sPassword)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_ChangePassword";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            cmdselect.Parameters.Add("@UserID", SqlDbType.VarChar).Value = UserID;

            cmdselect.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = sPassword;

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery(); 
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }

        public int ChangeEmail(int UserID, String sEmail)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_ChangeEmail";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            cmdselect.Parameters.Add("@UserID", SqlDbType.VarChar).Value = UserID;

            cmdselect.Parameters.Add("@EMAIL", SqlDbType.VarChar).Value = sEmail;

            cmdselect.Parameters.Add("@RESULTS", SqlDbType.Int);

            cmdselect.Parameters["@RESULTS"].Direction = ParameterDirection.Output;

            int Results = 0;

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();

                Results = (int)cmdselect.Parameters["@RESULTS"].Value;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return Results;
        }

        #endregion

        #region Schools
        public IList<SchoolModel> GetSchools()
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand("sp_GetSchools");

            cmdselect.CommandType = CommandType.StoredProcedure;

            da = new SqlDataAdapter();

            cmdselect.Connection = con;

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<SchoolModel> m_Schools = new List<SchoolModel>();
            try
            {
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        SchoolModel m = new SchoolModel();
                        m.iSchoolid = Convert.ToInt32(dt.Rows[i][0]);
                        m.sSchoolName = dt.Rows[i][1].ToString();
                        m.sProvince = dt.Rows[i][2].ToString();
                        m.sCity = dt.Rows[i][3].ToString();
                        m.sPostalAddress = dt.Rows[i][4].ToString();
                        m.sPhysicalAddress = dt.Rows[i][5].ToString();
                        m_Schools.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m_Schools; 
        }

        public IList<SchoolModel> GetSchoolsByParent(int UserID)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand("sp_GetSchoolsOnParent");

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Parameters.Add("@USERID", SqlDbType.Int).Value = UserID;

            da = new SqlDataAdapter();

            cmdselect.Connection = con;

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<SchoolModel> m_Schools = new List<SchoolModel>();
            try
            {
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        SchoolModel m = new SchoolModel();
                        m.iSchoolid = Convert.ToInt32(dt.Rows[i][0]);
                        m.sSchoolName = dt.Rows[i][1].ToString();
                        m.sProvince = dt.Rows[i][2].ToString();
                        m.sCity = dt.Rows[i][3].ToString();
                        m.sPostalAddress = dt.Rows[i][4].ToString();
                        m.sPhysicalAddress = dt.Rows[i][5].ToString();
                        m_Schools.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m_Schools;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="Userid"></param>
        /// <returns></returns>
        public IList<SchoolModel> GetSchoolsDataExtedend(int Userid)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand("sp_Getschoolbyparent");

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Parameters.Add("@USERID", SqlDbType.Int).Value = Userid;

            da = new SqlDataAdapter();

            cmdselect.Connection = con;

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<SchoolModel> m_Schools = new List<SchoolModel>();
            try
            {
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        SchoolModel m = new SchoolModel();
                        m.iSchoolid = Convert.ToInt32(dt.Rows[i][0]);
                        m.sSchoolName = dt.Rows[i][1].ToString();
                        m.sPhoto = (byte[])dt.Rows[i][2];
                        m_Schools.Add(m);
                    }
                }
            }
            catch (Exception ex)
            {
                
            }
            finally
            {
                con.Close();
            }
            return m_Schools;
        }
        #endregion

        #region Allergy
        public IList<AllergyModel> GetAllergies()
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand("sp_GetAllergies");

            cmdselect.CommandType = CommandType.StoredProcedure;

            da = new SqlDataAdapter();

            cmdselect.Connection = con;

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<AllergyModel> m_Allergy = new List<AllergyModel>();
            try
            {
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        AllergyModel m = new AllergyModel();
                        m.iAllergyID = Convert.ToInt32(dt.Rows[i][0]);
                        m.sAllergyName = dt.Rows[i][1].ToString();
                        m_Allergy.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m_Allergy;
        }

        #endregion

        #region Parent 

        public ParentModel GetParent(int Userid)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_GetParentDetails";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            cmdselect.Parameters.Add("@USERID", SqlDbType.Int).Value = Userid;
            
            ParentModel m = new ParentModel();

            DataTable dtParentDetails = new DataTable();

            da.SelectCommand = cmdselect;
            try
            {
                con.Open();

                da.Fill(dtParentDetails);

                if (dtParentDetails.Rows.Count > 0)
                {
                    for (int i = 0; i < dtParentDetails.Rows.Count; i++)
                    {
                        m.sFirstName = dtParentDetails.Rows[i][0].ToString();
                        m.sLastname = dtParentDetails.Rows[i][1].ToString();
                        m.sTelephone_work = dtParentDetails.Rows[i][2].ToString();
                        m.sFax = dtParentDetails.Rows[i][3].ToString();
                        m.sTel_home = dtParentDetails.Rows[i][4].ToString();
                        m.sContact_Cell = dtParentDetails.Rows[i][5].ToString();
                        m.sCOUNTRY = dtParentDetails.Rows[i][6].ToString();
                        m.sProvince = dtParentDetails.Rows[i][7].ToString();
                        m.sCity = dtParentDetails.Rows[i][8].ToString();
                        m.sEmergency_Contact = dtParentDetails.Rows[i][9].ToString();
                        m.sEmergency_Name = dtParentDetails.Rows[i][10].ToString();
                        m.iUserid = Convert.ToInt32(dtParentDetails.Rows[i][11]);
                        m.sEmail = dtParentDetails.Rows[i][12].ToString();
                        m.sADDRESS1 = dtParentDetails.Rows[i][13].ToString();
                        m.sADDRESS2 = dtParentDetails.Rows[i][14].ToString();
                        m.sADDRESS3 = dtParentDetails.Rows[i][15].ToString();
                        m.sADDRESS4 = dtParentDetails.Rows[i][16].ToString();
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m;
        }

        public void UpdateParentDetails(ParentModel ParentModel)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_UpdateParentInfo";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;
            
            if (ParentModel.sFirstName != null)
            {
                cmdselect.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value = ParentModel.sFirstName;
            }

            if (ParentModel.sLastname != null)
            {
                cmdselect.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value = ParentModel.sLastname;
            }

            if (ParentModel.sTelephone_work != null)
            {
                cmdselect.Parameters.Add("@TELEPHONE_WORK", SqlDbType.VarChar).Value = ParentModel.sTelephone_work;
            }

            if (ParentModel.sFax != null)
            {
                cmdselect.Parameters.Add("@FAX", SqlDbType.VarChar).Value = ParentModel.sFax;
            }

            if (ParentModel.sTel_home != null)
            {
                cmdselect.Parameters.Add("@TEL_HOME", SqlDbType.VarChar).Value = ParentModel.sTel_home;
            }

            if (ParentModel.sContact_Cell != null)
            {
                cmdselect.Parameters.Add("@CONTACT_CELLPHONE", SqlDbType.VarChar).Value = ParentModel.sContact_Cell;
            }

            if (ParentModel.sCity != null)
            {
                cmdselect.Parameters.Add("@CITY", SqlDbType.VarChar).Value = ParentModel.sCity;
            }

            if (ParentModel.sADDRESS1 != null)
            {
                cmdselect.Parameters.Add("@ADDRESS1", SqlDbType.VarChar).Value = ParentModel.sADDRESS1;
            }

            if (ParentModel.sADDRESS2 != null)
            {
                cmdselect.Parameters.Add("@ADDRESS2", SqlDbType.VarChar).Value = ParentModel.sADDRESS2;
            }

            if (ParentModel.sADDRESS3 != null)
            {
                cmdselect.Parameters.Add("@ADDRESS3", SqlDbType.VarChar).Value = ParentModel.sADDRESS3;
            }

            if (ParentModel.sADDRESS4 != null)
            {
                cmdselect.Parameters.Add("@ADDRESS4", SqlDbType.VarChar).Value = ParentModel.sADDRESS4;
            }

            if (ParentModel.sProvince != null)
            {
                cmdselect.Parameters.Add("@PROVINCE", SqlDbType.VarChar).Value = ParentModel.sProvince;
            }
            
            if (ParentModel.sEmergency_Contact != null)
            {
                cmdselect.Parameters.Add("@EMERGENCY_CONTACT", SqlDbType.VarChar).Value = ParentModel.sEmergency_Contact;
            }

            if (ParentModel.sEmergency_Name != null)
            {
                cmdselect.Parameters.Add("@EMERGENCY_NAME", SqlDbType.VarChar).Value = ParentModel.sEmergency_Name;
            }

            if (ParentModel.sCOUNTRY != null)
            {
                cmdselect.Parameters.Add("@COUNTRY", SqlDbType.VarChar).Value = ParentModel.sCOUNTRY;
            }
 
            cmdselect.Parameters.Add("@USERID", SqlDbType.VarChar).Value = ParentModel.iUserid;
        
            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }

        #endregion  

        #region Kids

        //Kid info

        public KidsModelExtended GetKids(int Userid)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_GetParentKids";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            cmdselect.Parameters.Add("@USERID", SqlDbType.Int).Value = Userid;

            KidsModelExtended m = new KidsModelExtended();

            DataTable dtParentDetails = new DataTable();

            da.SelectCommand = cmdselect;
            try
            {
                con.Open();

                da.Fill(dtParentDetails);

                if (dtParentDetails.Rows.Count > 0)
                {
                    for (int i = 0; i < dtParentDetails.Rows.Count; i++)
                    {
                        m.iKidid = Convert.ToInt32(dtParentDetails.Rows[i][0]);
                        m.sStudentNumber = dtParentDetails.Rows[i][1].ToString();
                        m.sKidName = dtParentDetails.Rows[i][2].ToString();
                        m.sKidLastname = dtParentDetails.Rows[i][3].ToString();
                        m.iSchoolID = Convert.ToInt32(dtParentDetails.Rows[i][4]);
                        m.iSchoolID = Convert.ToInt32(dtParentDetails.Rows[i][5]);
                        m.sKidGrade = dtParentDetails.Rows[i][6].ToString();
                        m.skidclass = dtParentDetails.Rows[i][7].ToString();
                        m.iAllergy = Convert.ToInt32(dtParentDetails.Rows[i][8]);
                        m.sBarcode = Convert.ToString(dtParentDetails.Rows[i][9]);
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m;
        }

        public IList<KidsModelExtended> GetKidsbyParent(int Userid)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_GetKidsbyParent";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            cmdselect.Parameters.Add("@USERID", SqlDbType.Int).Value = Userid;

            DataTable dtParentDetails = new DataTable();

            da.SelectCommand = cmdselect;
            List<KidsModelExtended> m_Logins = new List<KidsModelExtended>(); 

            try
            {
                con.Open();

                da.Fill(dtParentDetails);

                if (dtParentDetails.Rows.Count > 0)
                {
                    for (int i = 0; i < dtParentDetails.Rows.Count; i++)
                    {
                        KidsModelExtended m = new KidsModelExtended();
                        m.iKidid = Convert.ToInt32(dtParentDetails.Rows[i][0]);
                        m.sStudentNumber = dtParentDetails.Rows[i][1].ToString();
                        m.sKidName = dtParentDetails.Rows[i][2].ToString();
                        m.sKidLastname = dtParentDetails.Rows[i][3].ToString();
                        m.sSchoolName = dtParentDetails.Rows[i][4].ToString();
                        m.sKidGrade = dtParentDetails.Rows[i][5].ToString();
                        m.skidclass = dtParentDetails.Rows[i][6].ToString();
                        m.sAllergyName = dtParentDetails.Rows[i][7].ToString();
                        m.iSchoolID = Convert.ToInt32(dtParentDetails.Rows[i][8]);
                        m_Logins.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m_Logins;
        }
 
        public IList<KidsModelExtended> GetKidsList(int Userid)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_GetParentKidslist";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            cmdselect.Parameters.Add("@USERID", SqlDbType.Int).Value = Userid;

            List<KidsModelExtended> m_kids = new List<KidsModelExtended>();
            
            DataTable dtParentDetails = new DataTable();

            da.SelectCommand = cmdselect;
            try
            {
                con.Open();

                da.Fill(dtParentDetails);

                if (dtParentDetails.Rows.Count > 0)
                { 
                    for (int i = 0; i < dtParentDetails.Rows.Count; i++)
                    {
                        KidsModelExtended m = new KidsModelExtended();
                        m.iKidid = Convert.ToInt32(dtParentDetails.Rows[i][0]);
                        m.sKidName = dtParentDetails.Rows[i][1].ToString(); 
                        m_kids.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m_kids;
        }

        public int LinkKid(KidsModelExtended Model)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_Addkid";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con; 

            cmdselect.Parameters.Add("@KIDNAME", SqlDbType.VarChar).Value = Model.sKidName;
            cmdselect.Parameters.Add("@KIDLASTNAME", SqlDbType.VarChar).Value = Model.sKidLastname;
            cmdselect.Parameters.Add("@KIDSCHOOL", SqlDbType.Int).Value = Model.iSchoolID;
            cmdselect.Parameters.Add("@KIDGRADE", SqlDbType.VarChar).Value = Model.sKidGrade;
            cmdselect.Parameters.Add("@KIDCLASS", SqlDbType.VarChar).Value = Model.skidclass;
            cmdselect.Parameters.Add("@ALLERGY", SqlDbType.Int).Value = Model.iAllergy;
            cmdselect.Parameters.Add("@TEMPBARCODE", SqlDbType.VarChar).Value = Model.sBarcode;
            cmdselect.Parameters.Add("@STUDENTNUMBER", SqlDbType.VarChar).Value = Model.sStudentNumber;
            cmdselect.Parameters.Add("@USERID", SqlDbType.Int).Value = Model.iUserid;

            int Error = 0;

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();
                Error = 0;
            }
            catch (SqlException ex)
            {
                Error = 1;
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return Error;
        }

        public int UpdateKidsdetails(KidsModelExtended Model)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_UpdateKid";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            cmdselect.Parameters.Add("@KIDNAME", SqlDbType.VarChar).Value = Model.sKidName;
            cmdselect.Parameters.Add("@KIDLASTNAME", SqlDbType.VarChar).Value = Model.sKidLastname;
            cmdselect.Parameters.Add("@KIDSCHOOL", SqlDbType.Int).Value = Model.iSchoolID;
            cmdselect.Parameters.Add("@KIDGRADE", SqlDbType.VarChar).Value = Model.sKidGrade;
            cmdselect.Parameters.Add("@KIDCLASS", SqlDbType.VarChar).Value = Model.skidclass;
            cmdselect.Parameters.Add("@ALLERGY", SqlDbType.Int).Value = Model.iAllergy;
            cmdselect.Parameters.Add("@STUDENTNUMBER", SqlDbType.VarChar).Value = Model.sStudentNumber;
            cmdselect.Parameters.Add("@KidId", SqlDbType.Int).Value = Model.iKidid;

            int Error = 0;

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();
                Error = 0;
            }
            catch (SqlException ex)
            {
                Error = 1;
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return Error;
        }

        #endregion 

        #region Tuck-Shop
        public void UpdateTuckshopDetails(TuckShopModel TuckShopModel)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_UpdateTuckShopDetails";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            if (TuckShopModel.sTuckShopname != null)
            {
                cmdselect.Parameters.Add("@TUCKSHOPNAME", SqlDbType.VarChar).Value = TuckShopModel.sTuckShopname;
            }

            if (TuckShopModel.sSchoolname != null)
            {
                cmdselect.Parameters.Add("@SCHOOLNAME", SqlDbType.VarChar).Value = TuckShopModel.sSchoolname;
            }

            if (TuckShopModel.sTelephone_work != null)
            {
                cmdselect.Parameters.Add("@TELEPHONE_WORK", SqlDbType.VarChar).Value = TuckShopModel.sTelephone_work;
            }

            if (TuckShopModel.sFax != null)
            {
                cmdselect.Parameters.Add("@FAX", SqlDbType.VarChar).Value = TuckShopModel.sFax;
            }

            if (TuckShopModel.sTel_home != null)
            {
                cmdselect.Parameters.Add("@TEL_HOME", SqlDbType.VarChar).Value = TuckShopModel.sTel_home;
            }
            
            if (TuckShopModel.sPhysical_Address != null)
            {
                cmdselect.Parameters.Add("@PHYSICAL_ADDRESS", SqlDbType.VarChar).Value = TuckShopModel.sPhysical_Address;
            }

            if (TuckShopModel.sPostal_Address != null)
            {
                cmdselect.Parameters.Add("@POSTAL_ADDRESS", SqlDbType.VarChar).Value = TuckShopModel.sPostal_Address;
            }

            if (TuckShopModel.sProvince != null)
            {
                cmdselect.Parameters.Add("@PROVINCE", SqlDbType.VarChar).Value = TuckShopModel.sProvince;
            }
 
            if (TuckShopModel.iUserid != 0)
            {
                cmdselect.Parameters.Add("@USERID", SqlDbType.VarChar).Value = TuckShopModel.iUserid;
            }

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }

        public TuckShopModel GetTuckShopDetails(int Userid)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_GetTuckshopDetails";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            cmdselect.Parameters.Add("@USERID", SqlDbType.Int).Value = Userid;

            TuckShopModel m = new TuckShopModel();

            DataTable dtParentDetails = new DataTable();

            da.SelectCommand = cmdselect;
            try
            {
                con.Open();

                da.Fill(dtParentDetails);

                if (dtParentDetails.Rows.Count > 0)
                {
                    for (int i = 0; i < dtParentDetails.Rows.Count; i++)
                    {
                        m.sTuckShopname = dtParentDetails.Rows[i][0].ToString();
                        m.sSchoolname = dtParentDetails.Rows[i][1].ToString();
                        m.sTelephone_work = dtParentDetails.Rows[i][2].ToString();
                        m.sFax = dtParentDetails.Rows[i][3].ToString();
                        m.sTel_home = dtParentDetails.Rows[i][4].ToString();
                        m.sProvince = dtParentDetails.Rows[i][5].ToString();
                        m.sPostal_Address = dtParentDetails.Rows[i][6].ToString();
                        m.sPhysical_Address = dtParentDetails.Rows[i][7].ToString();
                        m.iUserid = Convert.ToInt32(dtParentDetails.Rows[i][8]);
                        m.sEmail = dtParentDetails.Rows[i][9].ToString();
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m;
        }

        #endregion 

        #region Contacts 
        public IList<ContactsModel> GetContactswork(int Userid)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_GetContacts_work";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            cmdselect.Parameters.Add("@USERID", SqlDbType.Int).Value = Userid;

            DataTable dtContactDetails = new DataTable();

            List<ContactsModel> m_Contacts = new List<ContactsModel>(); 

            da.SelectCommand = cmdselect;
            try
            {
                con.Open();

                da.Fill(dtContactDetails);

                if (dtContactDetails.Rows.Count > 0)
                {
                    for (int i = 0; i < dtContactDetails.Rows.Count; i++)
                    {
                        ContactsModel m = new ContactsModel();
                        m.iUserid = Convert.ToInt32(dtContactDetails.Rows[i][0]);
                        m.sContactNumber = dtContactDetails.Rows[i][1].ToString();
                        m.sComment = dtContactDetails.Rows[i][2].ToString();
                        m.sIsMain = Convert.ToBoolean(dtContactDetails.Rows[i][3]);
                        m.sCategory = dtContactDetails.Rows[i][4].ToString();
                        m.iContactNumberID = Convert.ToInt32(dtContactDetails.Rows[i][5]);
                        m_Contacts.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m_Contacts;
        }

        public void DeleteContactnumber(int ContactNumberID)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_DeleteContactnumber";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            cmdselect.Parameters.Add("@CONTACTNUMBERID", SqlDbType.Int).Value = ContactNumberID; 

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }

        public IList<ContactsModel> GetContactHome(int Userid)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_GetContacts_Home";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            cmdselect.Parameters.Add("@USERID", SqlDbType.Int).Value = Userid;

            DataTable dtContactDetails = new DataTable();

            List<ContactsModel> m_Contacts = new List<ContactsModel>(); 

            da.SelectCommand = cmdselect;
            try
            {
                con.Open();

                da.Fill(dtContactDetails);

                if (dtContactDetails.Rows.Count > 0)
                {
                    for (int i = 0; i < dtContactDetails.Rows.Count; i++)
                    {
                        ContactsModel m = new ContactsModel();
                        m.iUserid = Convert.ToInt32(dtContactDetails.Rows[i][0]);
                        m.sContactNumber = dtContactDetails.Rows[i][1].ToString();
                        m.sComment = dtContactDetails.Rows[i][2].ToString();
                        m.sIsMain = Convert.ToBoolean(dtContactDetails.Rows[i][3]);
                        m.sCategory = dtContactDetails.Rows[i][4].ToString();
                        m.iContactNumberID = Convert.ToInt32(dtContactDetails.Rows[i][5]);
                        m_Contacts.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m_Contacts;
        }
        
        public IList<ContactsModel> GetContactCellphone(int Userid)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_GetContacts_Cellphone";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            cmdselect.Parameters.Add("@USERID", SqlDbType.Int).Value = Userid;

            DataTable dtContactDetails = new DataTable();

            List<ContactsModel> m_Contacts = new List<ContactsModel>(); 

            da.SelectCommand = cmdselect;
            try
            {
                con.Open();

                da.Fill(dtContactDetails);

                if (dtContactDetails.Rows.Count > 0)
                {
                    for (int i = 0; i < dtContactDetails.Rows.Count; i++)
                    {
                        ContactsModel m = new ContactsModel();

                        m.iUserid = Convert.ToInt32(dtContactDetails.Rows[i][0]);
                        m.sContactNumber = dtContactDetails.Rows[i][1].ToString();
                        m.sComment = dtContactDetails.Rows[i][2].ToString();
                        m.sIsMain = Convert.ToBoolean(dtContactDetails.Rows[i][3]);
                        m.sCategory = dtContactDetails.Rows[i][4].ToString();
                        m.iContactNumberID = Convert.ToInt32(dtContactDetails.Rows[i][5]);
                        m_Contacts.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m_Contacts;
        }

        public IList<ContactsModel> GetContactEmergency(int Userid)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_GetContacts_Emergency";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            cmdselect.Parameters.Add("@USERID", SqlDbType.Int).Value = Userid;

            DataTable dtContactDetails = new DataTable();

            List<ContactsModel> m_Contacts = new List<ContactsModel>(); 

            da.SelectCommand = cmdselect;
            try
            {
                con.Open();

                da.Fill(dtContactDetails);

                if (dtContactDetails.Rows.Count > 0)
                {
                    for (int i = 0; i < dtContactDetails.Rows.Count; i++)
                    {
                        ContactsModel m = new ContactsModel();

                        m.iUserid = Convert.ToInt32(dtContactDetails.Rows[i][0]);
                        m.sContactNumber = dtContactDetails.Rows[i][1].ToString();
                        m.sComment = dtContactDetails.Rows[i][2].ToString();
                        m.sIsMain = Convert.ToBoolean(dtContactDetails.Rows[i][3]);
                        m.sCategory = dtContactDetails.Rows[i][4].ToString();
                        m.iContactNumberID = Convert.ToInt32(dtContactDetails.Rows[i][5]);
                        m_Contacts.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m_Contacts;
        }

        public int AddContactNumber(ContactsModel Model)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_AddContactNumber";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            cmdselect.Parameters.Add("@USERID", SqlDbType.Int).Value = Model.iUserid;
            cmdselect.Parameters.Add("@CONTACTNUMBER", SqlDbType.VarChar).Value = Model.sContactNumber;
            cmdselect.Parameters.Add("@COMMENT", SqlDbType.VarChar).Value = Model.sComment;
            cmdselect.Parameters.Add("@CATEGORY", SqlDbType.VarChar).Value = Model.sCategory; 

            int Error = 0;

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();
                Error = 0;
            }
            catch (SqlException ex)
            {
                Error = 1;
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return Error; 
        }

        public int UpdateContactNumber(ContactsModel Model)
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand();

            cmdselect.CommandText = "sp_UpdateContactNumbers";

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.Connection = con;

            cmdselect.Parameters.Add("@CONTACTNUMBERID", SqlDbType.Int).Value = Model.iContactNumberID;
            cmdselect.Parameters.Add("@CONTACTNUMBER", SqlDbType.VarChar).Value = Model.sContactNumber;
            cmdselect.Parameters.Add("@COMMENT", SqlDbType.VarChar).Value = Model.sComment;
            cmdselect.Parameters.Add("@ISMAIN", SqlDbType.Bit).Value = Model.sIsMain;
            cmdselect.Parameters.Add("@USERID", SqlDbType.Int).Value = Model.iUserid;
            cmdselect.Parameters.Add("@CATEGORY", SqlDbType.VarChar).Value = Model.sCategory;

            int Error = 0;

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();
                Error = 0;
            }
            catch (SqlException ex)
            {
                Error = 1;
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return Error;
        }

        #region General

        #endregion

        #endregion

        #region General Methods

        public IList<CountryModel> GetCountries()
        {
            con = new SqlConnection(strCon);

            cmdselect = new SqlCommand("sp_Countries");

            cmdselect.CommandType = CommandType.StoredProcedure;

            da = new SqlDataAdapter();

            cmdselect.Connection = con;

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<CountryModel> m_Countries = new List<CountryModel>();
            try
            {
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        CountryModel m = new CountryModel();
                        m.iCountryID = Convert.ToInt32(dt.Rows[i][0]);
                        m.sCountryName = dt.Rows[i][1].ToString();
                        m_Countries.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m_Countries;
        }

        #endregion
        //HE Functionalities
        #region Ministries
        /// <summary>
        /// Get Ministries 
        /// </summary>
        /// <returns></returns>
        public IList<MinistryModel> GetMinistries()
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandText = "uspSelectMinistries";

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dtMinistries = new DataTable();

            List<MinistryModel> m_Minstries = new List<MinistryModel>();
            try
            {
                da.Fill(dtMinistries);
                if (dtMinistries.Rows.Count > 0)
                {
                    for (int i = 0; i < dtMinistries.Rows.Count; i++)
                    {
                        MinistryModel m = new MinistryModel();
                        m.iMinistryid = Convert.ToInt32(dtMinistries.Rows[i][0]);
                        m.sMinistryName = dtMinistries.Rows[i][1].ToString();
                        m_Minstries.Add(m);
                    }
                }
            }
            catch (Exception ex)
            {
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }

            return m_Minstries;
        }

        #endregion

        #region Acts And Bills
        /// <summary>
        /// Add Acts and Bills
        /// </summary>
        /// <param name="_dtDate_Received"></param>
        /// <param name="_iMinistry_ID__Received_from"></param>
        /// <param name="_sSubject_Title_ofbill_Act_Ref"></param>
        /// <param name="_dtDate_to_Attorney_General"></param>
        /// <param name="_dtDate_received_from_Attorney_General"></param>
        /// <param name="_dtDate_send_for_minister_Signature"></param>
        /// <param name="_dtDate_send_to_Committee_secritary"></param>
        /// <param name="_dtDate_send_to_HE_signature"></param>
        /// <param name="_dtDate_signed_by_HE"></param>
        /// <param name="_dtDate_send_to_parliament"></param>
        /// <param name="_dtDate_send_for_gazetting"></param>
        /// <param name="_dtDate_gazetted"></param>
        /// <param name="_dtDate_received_from_HE"></param>
        /// <param name="_dtSealed_yes_No"></param>
        /// <param name="_sReferencenumber"></param>
        /// <param name="_dtGeneral_Comments"></param>
        /// <param name="_dtDate_entered"></param>
        /// <param name="_sEntered_b"></param>
        public String AddActsBills(ActsAndBillsModel Actsandbills)
        {
            con = new SqlConnection(Strcon);
             
            cmdinsert = new SqlCommand();
            cmdinsert.CommandType = CommandType.StoredProcedure;
            cmdinsert.CommandText = "uspActsandbillsInsertOne";
            cmdinsert.Parameters.Add("@Date_Received", SqlDbType.DateTime).Value = Actsandbills.DateReceived;
            cmdinsert.Parameters.Add("@Ministry_ID_Received_from", SqlDbType.Int).Value = Actsandbills.MinistryID;
            cmdinsert.Parameters.Add("@Subject_Title_ofbill_Act_Ref", SqlDbType.VarChar).Value = Actsandbills.SubjectTitleofbilActRef;
            cmdinsert.Parameters.Add("@Date_to_Attorney_General", SqlDbType.DateTime).Value = Actsandbills.DatereceivedfromAG;
            cmdinsert.Parameters.Add("@Date_received_from_Attorney_General", SqlDbType.DateTime).Value = Actsandbills.DatereceivedfromAG;
            cmdinsert.Parameters.Add("@Date_send_for_minister_Signature", SqlDbType.DateTime).Value = Actsandbills.DatesendMSignature;
            cmdinsert.Parameters.Add("@Date_send_to_Committee_secritary", SqlDbType.DateTime).Value = Actsandbills.DatesendCS;
            cmdinsert.Parameters.Add("@Date_send_to_HE_signature", SqlDbType.DateTime).Value = Actsandbills.DatesendHEsignature;
            cmdinsert.Parameters.Add("@Date_signed_by_HE", SqlDbType.DateTime).Value = Actsandbills.DatesignedHE;
            cmdinsert.Parameters.Add("@Date_send_to_national_archives", SqlDbType.DateTime).Value = Actsandbills.DatesendNA;
            cmdinsert.Parameters.Add("@Date_send_to_parliament", SqlDbType.DateTime).Value = Actsandbills.DatesendP;
            cmdinsert.Parameters.Add("@Date_send_for_gazetting", SqlDbType.DateTime).Value = Actsandbills.DatesendGazetting;
            cmdinsert.Parameters.Add("@Date_gazetted", SqlDbType.DateTime).Value = Actsandbills.DateGazetted;
            cmdinsert.Parameters.Add("@Date_received_from_HE", SqlDbType.DateTime).Value = Actsandbills.DatereceivedfromHE;
            cmdinsert.Parameters.Add("@Sealed_yes_No", SqlDbType.Bit).Value = Actsandbills.Sealed;
            cmdinsert.Parameters.Add("@Referencenumber", SqlDbType.VarChar).Value = Actsandbills.Sealed;
            cmdinsert.Parameters.Add("@General_Comments", SqlDbType.VarChar).Value = Actsandbills.Referencenumber;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Actsandbills.Dateentered; 
            cmdinsert.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = Actsandbills.Enteredby;
            cmdinsert.Connection = con;
                                     
            //Procedure or function 'uspActsandbillsInsertOne' expects parameter '@Entered_by', which was not supplied.
            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;// "RECORD NOT ADDED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public String UpdatedActsBills(ActsAndBillsModel Actsandbills)
        {
            con = new SqlConnection(Strcon);

            cmdUpdate = new SqlCommand();

            cmdUpdate.CommandType = CommandType.StoredProcedure;

            cmdUpdate.CommandText = "usp_UpdateActsAndBills";

            //Check the Parameters that needs to be Updated(Date parameters the Problematic ones)

            cmdUpdate.Parameters.Add("@SubjectTitleofbilActRef", SqlDbType.VarChar).Value = Actsandbills.SubjectTitleofbilActRef;
            cmdUpdate.Parameters.Add("@DateReceived", SqlDbType.DateTime).Value = Actsandbills.DateReceived;
            cmdUpdate.Parameters.Add("@MinistryID", SqlDbType.Int).Value = Actsandbills.MinistryID;
            cmdUpdate.Parameters.Add("@DatetoAG", SqlDbType.DateTime).Value = Actsandbills.DatereceivedfromAG;
            cmdUpdate.Parameters.Add("@DatereceivedfromAG", SqlDbType.DateTime).Value = Actsandbills.DatereceivedfromAG;
            cmdUpdate.Parameters.Add("@DatesendMSignature", SqlDbType.DateTime).Value = Actsandbills.DatesendMSignature;
            cmdUpdate.Parameters.Add("@DatesendCS", SqlDbType.DateTime).Value = Actsandbills.DatesendCS;
            cmdUpdate.Parameters.Add("@DatesendHEsignature", SqlDbType.DateTime).Value = Actsandbills.DatesendHEsignature;
            cmdUpdate.Parameters.Add("@DatesignedHE", SqlDbType.DateTime).Value = Actsandbills.DatesignedHE;
            cmdUpdate.Parameters.Add("@DatesendNA", SqlDbType.DateTime).Value = Actsandbills.DatesendNA;
            cmdUpdate.Parameters.Add("@DatesendP", SqlDbType.DateTime).Value = Actsandbills.DatesendP;
            cmdUpdate.Parameters.Add("@DatesendGazetting", SqlDbType.DateTime).Value = Actsandbills.DatesendGazetting;
            cmdUpdate.Parameters.Add("@DateGazetted", SqlDbType.DateTime).Value = Actsandbills.DateGazetted;
            cmdUpdate.Parameters.Add("@DatereceivedfromHE", SqlDbType.DateTime).Value = Actsandbills.DatereceivedfromHE;
            cmdUpdate.Parameters.Add("@Sealed", SqlDbType.Bit).Value = Actsandbills.Sealed;
            cmdUpdate.Parameters.Add("@Referencenumber", SqlDbType.VarChar).Value = Actsandbills.Referencenumber;
            cmdUpdate.Parameters.Add("@GeneralComments", SqlDbType.VarChar).Value = Actsandbills.GeneralComments;
            cmdUpdate.Parameters.Add("@Dateentered", SqlDbType.DateTime).Value = Actsandbills.Dateentered;
            cmdUpdate.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = Actsandbills.Enteredby;
            cmdUpdate.Parameters.Add("@ActsandbillsID", SqlDbType.Int).Value = Actsandbills.iActsandbillsid;
            cmdUpdate.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdUpdate.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message; //"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        /// <summary>
        ///  Search the Acts and Bills Dynamic Search
        /// </summary>
        /// <param name="_iActsandbillsID"></param>
        /// <param name="_dtDateReceived"></param>
        /// <returns></returns>
        public IList<ActsAndBillsModel> GetActsbills(ActsAndBillsModel Actsandbills)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "usp_Searchbill";

            if (Actsandbills.Referencenumber != null)
            {
                cmdselect.Parameters.Add("@Referencenumber", SqlDbType.VarChar).Value = Actsandbills.Referencenumber;
            }

            if (Actsandbills.Dateentered != null && Actsandbills.Dateentered != Convert.ToDateTime("0001/01/01 12:00:00 AM"))
            {
                cmdselect.Parameters.Add("@DateReceived", SqlDbType.DateTime).Value = Actsandbills.DateReceived;
            }
            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dtactsbills = new DataTable();

            List<ActsAndBillsModel> m_ActsAndbills = new List<ActsAndBillsModel>();
            try
            {
                con.Open();

                da.Fill(dtactsbills);
                if (dtactsbills.Rows.Count > 0)
                {
                    for (int i = 0; i < dtactsbills.Rows.Count; i++)
                    {
                        ActsAndBillsModel m = new ActsAndBillsModel();
                        m.iActsandbillsid = Convert.ToInt32(dtactsbills.Rows[i][0]); 
                        m.SubjectTitleofbilActRef = dtactsbills.Rows[i][1].ToString();
                        m.DateReceived = Convert.ToDateTime(dtactsbills.Rows[i][2]);
                        m.MinistryID = Convert.ToInt32(dtactsbills.Rows[i][3]);
                        m.DatetoAG = Convert.ToDateTime(dtactsbills.Rows[i][4]);
                        m.DatereceivedfromAG = Convert.ToDateTime(dtactsbills.Rows[i][5]);
                        m.DatesendMSignature = Convert.ToDateTime(dtactsbills.Rows[i][6]);
                        m.DatesendCS = Convert.ToDateTime(dtactsbills.Rows[i][7]);
                        m.DatesendHEsignature = Convert.ToDateTime(dtactsbills.Rows[i][8]);
                        m.DatesignedHE = Convert.ToDateTime(dtactsbills.Rows[i][9]);
                        m.DatesendNA = Convert.ToDateTime(dtactsbills.Rows[i][10]);
                        m.DatesendP = Convert.ToDateTime(dtactsbills.Rows[i][11]);
                        m.DatesendGazetting = Convert.ToDateTime(dtactsbills.Rows[i][12]);
                        m.DateGazetted = Convert.ToDateTime(dtactsbills.Rows[i][13]);
                        m.DatereceivedfromHE = Convert.ToDateTime(dtactsbills.Rows[i][14]);
                        m.Sealed = Convert.ToBoolean(dtactsbills.Rows[i][15]);
                        m.Referencenumber = dtactsbills.Rows[i][16].ToString();
                        m.GeneralComments = dtactsbills.Rows[i][17].ToString();
                        m.Dateentered = Convert.ToDateTime(dtactsbills.Rows[i][18]);
                        m.Enteredby = dtactsbills.Rows[i][19].ToString(); 
                        m_ActsAndbills.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_ActsAndbills;
        }

        public String DeleteActsBills(int _iActsAndBillID)
        {
            con = new SqlConnection(Strcon);

            cmdDelete = new SqlCommand();

            cmdDelete.CommandType = CommandType.StoredProcedure;

            cmdDelete.CommandText = "usp_DeleteActsAndBills";

            cmdDelete.Parameters.Add("@ActsandbillsID", SqlDbType.Int).Value = _iActsAndBillID;

            cmdDelete.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdDelete.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message; // "RECORD NOT DELETED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        #endregion

        #region Logging

        /// <summary>
        /// Insert Log
        /// </summary>
        /// <param name="_iUserID"></param>
        /// <param name="_iLoginID"></param>
        /// <param name="_sLogAction"></param>
        /// <returns></returns>
        public int InsertLogin(int iUserID, int iLoginID, String sLogAction)
        {
            int iLogID = 0;

            Dictionary<string, object> lstParam = new Dictionary<string, object>();

            try
            {
                lstParam.Add("UserID", iUserID);

                lstParam.Add("LoginID", iLoginID);

                lstParam.Add("Action", sLogAction);
            }
            catch (Exception ex)
            {
                iLogID = 0;
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                // _Registrydb.Dispose();
            }

            return iLogID;
        }

        #endregion

        #region Cabinet Authorities

        #region InComming
        /// <summary>
        /// DeleteCabinetAuthorities
        /// </summary>
        /// <param name="_iIncomingCabinetID"></param>
        /// <returns>String</returns>
        public String DeleteIncommingCabinetAuthorities(int iIncomingCabinetID)
        {
            con = new SqlConnection(Strcon);

            cmdDelete = new SqlCommand();

            cmdDelete.CommandType = CommandType.StoredProcedure;

            cmdDelete.CommandText = "usp_delete_IncomingCabinetauthority";

            cmdDelete.Parameters.Add("@IncomingCabinetID", SqlDbType.Int).Value = iIncomingCabinetID;

            cmdDelete.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdDelete.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "Unable to Delete Authority";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        /// <summary>
        /// AddCabinetAuthority
        /// </summary>
        /// <param name="_iMinistryID"></param>
        /// <param name="_sReferencenumber"></param>
        /// <param name="_sTitle"></param>
        /// <param name="_sDestinationw"></param>
        /// <param name="_sReferedto"></param>
        /// <param name="_dtDaterecorded"></param>
        /// <param name="_dtDateentered"></param>
        /// <param name="_sEnteredby"></param>
        /// <returns>String</returns>
        public String AddCabinetAuthority(CabinetAuthoritiesModel Model)
        {
            con = new SqlConnection(Strcon);

            cmdinsert = new SqlCommand();

            cmdinsert.CommandType = CommandType.StoredProcedure;

            cmdinsert.CommandText = "usp_IncomingCabinetAuthorityInsertOne";

            cmdinsert.Parameters.Add("@MinistryID", SqlDbType.Int).Value = Model.MinistryID;
            cmdinsert.Parameters.Add("@Referencenumber", SqlDbType.NVarChar).Value = Model.Referencenumber;
            cmdinsert.Parameters.Add("@Title", SqlDbType.NVarChar).Value = Model.Title;
            cmdinsert.Parameters.Add("@Destinationw", SqlDbType.NVarChar).Value = Model.Destination;
            cmdinsert.Parameters.Add("@Referedto", SqlDbType.NVarChar).Value = Model.Referedto;
            cmdinsert.Parameters.Add("@Daterecorded", SqlDbType.DateTime).Value = Model.Daterecorded;
            cmdinsert.Parameters.Add("@Dateentered", SqlDbType.DateTime).Value = Model.Dateentered;
            cmdinsert.Parameters.Add("@Enteredby", SqlDbType.NVarChar).Value = Model.Enteredby;
            cmdinsert.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "Unable to Add Authority";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="_sTITLE"></param>
        /// <param name="_sREFERENCENUMBER"></param>
        /// <returns>DataTable</returns>
        public IList<CabinetAuthoritiesModel> GetCabinetAuthoritiesBill(String sTITLE, String sREFERENCENUMBER)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandText = "uspSearchCabinetAuthoritiesBill";

            if (sTITLE != String.Empty)
            {
                cmdselect.Parameters.Add("@SUBJECT", SqlDbType.VarChar).Value = sTITLE;
            }

            if (sREFERENCENUMBER != String.Empty)
            {
                cmdselect.Parameters.Add("@Referencenumber", SqlDbType.VarChar).Value = sREFERENCENUMBER;
            }

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dtCabinet = new DataTable();

            List<CabinetAuthoritiesModel> m_Minstries = new List<CabinetAuthoritiesModel>();

            try
            {
                con.Open();

                da.Fill(dtCabinet);

                if (dtCabinet.Rows.Count > 0)
                {
                    for (int i = 0; i < dtCabinet.Rows.Count; i++)
                    {
                        CabinetAuthoritiesModel m = new CabinetAuthoritiesModel();
                        m.IncomingCabinetID = Convert.ToInt32(dtCabinet.Rows[i][0]);
                        m.MinistryID = Convert.ToInt32(dtCabinet.Rows[i][1]);
                        m.Referencenumber = dtCabinet.Rows[i][2].ToString();
                        m.Title = dtCabinet.Rows[i][3].ToString();
                        m.Destination = dtCabinet.Rows[i][4].ToString();
                        m.Referedto = dtCabinet.Rows[i][5].ToString();
                        m.Daterecorded = Convert.ToDateTime(dtCabinet.Rows[i][6]);
                        m.Dateentered = Convert.ToDateTime(dtCabinet.Rows[i][7]);
                        m.Enteredby = dtCabinet.Rows[i][8].ToString();
                        m_Minstries.Add(m);
                    }
                }
            }
            catch (Exception ex)
            {
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return m_Minstries;
        }

        //Dispatch
        /// <summary>
        /// AddCabinetAuthorised
        /// </summary>
        /// <param name="_sReferencenumber"></param>
        /// <param name="_sCollectedby"></param>
        /// <param name="_dtDate_collected"></param>
        /// <param name="_dtDateentered"></param>
        /// <param name="_sEnteredby"></param>
        /// <param name="_sNamesofOfficials"></param>
        /// <param name="_sDestination"></param>
        /// <returns>String</returns>
        public String AddCabinetAuthorised(String sReferencenumber, String sCollectedby, DateTime dtDate_collected
                                           , DateTime dtDateentered, String sEnteredby, String sNamesofOfficials, String sDestination)
        {
            con = new SqlConnection(Strcon);

            cmdinsert = new SqlCommand();

            cmdinsert.CommandType = CommandType.StoredProcedure;

            cmdinsert.CommandText = "usp_CabinetauthorisedInsertOne";

            cmdinsert.Parameters.Add("@Referencenumber", SqlDbType.NVarChar).Value = sReferencenumber;
            cmdinsert.Parameters.Add("@Collectedby", SqlDbType.NVarChar).Value = sCollectedby;
            cmdinsert.Parameters.Add("@Date_collected", SqlDbType.DateTime).Value = dtDate_collected;
            cmdinsert.Parameters.Add("@Entered_by", SqlDbType.NVarChar).Value = sEnteredby;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = dtDateentered;
            cmdinsert.Parameters.Add("@NamesofOfficials", SqlDbType.NVarChar).Value = sNamesofOfficials;
            cmdinsert.Parameters.Add("@Destination", SqlDbType.NVarChar).Value = sDestination;
            cmdinsert.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "Unable to Add Authority";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public String UpdateCabinetAuthority(CabinetAuthoritiesModel model)
        {
            con = new SqlConnection(Strcon);

            cmdUpdate = new SqlCommand();

            cmdUpdate.CommandType = CommandType.StoredProcedure;

            cmdUpdate.CommandText = "qryIncomingCabinetAuthorityEditOne";
            cmdUpdate.Parameters.Add("@IncomingCabinetID", SqlDbType.Int).Value = model.IncomingCabinetID;// _sReferencenumber;
            cmdUpdate.Parameters.Add("@MinistryID", SqlDbType.Int).Value = model.MinistryID;// _sReferencenumber;
            cmdUpdate.Parameters.Add("@Referencenumber", SqlDbType.VarChar).Value = model.Referencenumber;// _sCollectedby;
            cmdUpdate.Parameters.Add("@Title", SqlDbType.VarChar).Value = model.Title;// _dtDate_collected;
            cmdUpdate.Parameters.Add("@Destinationw", SqlDbType.VarChar).Value = model.Destination;// _sEnteredby;
            cmdUpdate.Parameters.Add("@Referedto", SqlDbType.VarChar).Value = model.Referedto;// _dtDateentered;
            cmdUpdate.Parameters.Add("@Daterecorded", SqlDbType.DateTime).Value = model.Daterecorded;// _sNamesofOfficials;
            cmdUpdate.Parameters.Add("@Dateentered", SqlDbType.DateTime).Value = model.Dateentered;// _sDestination;
            cmdUpdate.Parameters.Add("@Enteredby", SqlDbType.VarChar).Value = model.Enteredby;// _sDestination;

            cmdUpdate.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdUpdate.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "Unable to Add Authority";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public String UpdateCabinetAuthorised(int iCabinet_minutes_ID, DateTime dtDate_received, DateTime dtDate_of_cabinet_minutes
                                              , string sCabinet_minutes_number, String sSubject2, String sSubject_purpose_reading, int iMinistry_ID,
                                              string soriginator_s_names, byte btApproved_unapproved__Yes_No, int iReceieved_by_ID,
                                              DateTime dtDate_approved_unapproved, string sEntered_by, DateTime dtDate_entered, string sDelivery, DateTime dtDispatchdate)
        {
            con = new SqlConnection(Strcon);

            cmdUpdate = new SqlCommand();

            cmdUpdate.CommandType = CommandType.StoredProcedure;

            cmdUpdate.CommandText = "usp_CabinetminutesEditOne";
            cmdUpdate.Parameters.Add("@Cabinet_minutes_ID", SqlDbType.Int).Value = iCabinet_minutes_ID;
            cmdUpdate.Parameters.Add("@Date_received", SqlDbType.DateTime).Value = dtDate_received;
            cmdUpdate.Parameters.Add("@Date_of_cabinet_minutes", SqlDbType.NVarChar).Value = dtDate_of_cabinet_minutes;
            cmdUpdate.Parameters.Add("@cabinet_minutes_number", SqlDbType.NVarChar).Value = sCabinet_minutes_number;
            cmdUpdate.Parameters.Add("@Subject2", SqlDbType.NVarChar).Value = sSubject2;
            cmdUpdate.Parameters.Add("@Subject_purpose_reading", SqlDbType.NVarChar).Value = sSubject_purpose_reading;
            cmdUpdate.Parameters.Add("@Ministry_ID", SqlDbType.Int).Value = iMinistry_ID;
            cmdUpdate.Parameters.Add("@originator_s_names", SqlDbType.NVarChar).Value = soriginator_s_names;
            cmdUpdate.Parameters.Add("@Approved_unapproved__Yes_No", SqlDbType.Bit).Value = btApproved_unapproved__Yes_No;
            cmdUpdate.Parameters.Add("@Receieved_by_ID", SqlDbType.Int).Value = iReceieved_by_ID;
            cmdUpdate.Parameters.Add("@Date_approved_unapproved", SqlDbType.DateTime).Value = dtDate_approved_unapproved;
            cmdUpdate.Parameters.Add("@Entered_by", SqlDbType.NVarChar).Value = sEntered_by;
            cmdUpdate.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = dtDate_entered;
            cmdUpdate.Parameters.Add("@Delivery", SqlDbType.NVarChar).Value = sDelivery;
            cmdUpdate.Parameters.Add("@Dispatchdate", SqlDbType.DateTime).Value = dtDispatchdate;
            cmdUpdate.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdUpdate.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "Unable to Update a Cabinet Authority";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        #endregion

        #region Dispatch
        public String AddDispatchcabinetauthority(DispatchCabinetAuthorityModel Model)
        {
            con = new SqlConnection(Strcon);

            cmdinsert = new SqlCommand();

            cmdinsert.CommandType = CommandType.StoredProcedure;

            cmdinsert.CommandText = "SPInsertDispatchcabinetauthority";

            cmdinsert.Parameters.Add("@Referencenumber", SqlDbType.VarChar).Value = Model.Referencenumber;
            cmdinsert.Parameters.Add("@Collectedby", SqlDbType.VarChar).Value = Model.Collectedby;
            cmdinsert.Parameters.Add("@Date_collected", SqlDbType.DateTime).Value = Model.Datecollected;
            cmdinsert.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = Model.Enteredby;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Dateentered;
            cmdinsert.Parameters.Add("@NamesofOfficials", SqlDbType.VarChar).Value = Model.NamesofOfficials;
            cmdinsert.Parameters.Add("@Destination", SqlDbType.VarChar).Value = Model.Destination;
            cmdinsert.Connection = con;
  
            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "Unable to Add Authority";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public String UpdateDispatchcabinetauthority(DispatchCabinetAuthorityModel Model)
        {
            con = new SqlConnection(Strcon);

            cmdinsert = new SqlCommand();

            cmdinsert.CommandType = CommandType.StoredProcedure;

            cmdinsert.CommandText = "qryCabinetAuthorisedEditOne";

            cmdinsert.Parameters.Add("@CabinetauthorityID", SqlDbType.Int).Value = Model.CabinetauthorityID;
            cmdinsert.Parameters.Add("@Referencenumber", SqlDbType.VarChar).Value = Model.Referencenumber;
            cmdinsert.Parameters.Add("@Collectedby", SqlDbType.VarChar).Value = Model.Collectedby;
            cmdinsert.Parameters.Add("@Date_collected", SqlDbType.DateTime).Value = Model.Datecollected;
            cmdinsert.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = Model.Enteredby;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Dateentered;
            cmdinsert.Parameters.Add("@NamesofOfficials", SqlDbType.VarChar).Value = Model.NamesofOfficials;
            cmdinsert.Parameters.Add("@Destination", SqlDbType.VarChar).Value = Model.Destination;
            cmdinsert.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message; //"Unable to Update Dispatch Authority";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public IList<DispatchCabinetAuthorityModel> GetDispatchcabinetauthority(String _sREFERENCENUMBER, String _sYear)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandText = "uspSearchDispatchcabinetauthority";

            if (_sREFERENCENUMBER != String.Empty)
            {
                cmdselect.Parameters.Add("@REFERENCENUMBER", SqlDbType.VarChar).Value = _sREFERENCENUMBER;
            }

            if (_sYear != String.Empty)
            {
                cmdselect.Parameters.Add("@Year", SqlDbType.VarChar).Value = _sYear;
            }

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dtCabinet = new DataTable();

            List<DispatchCabinetAuthorityModel> m_Minstries = new List<DispatchCabinetAuthorityModel>();
            try
            { 
                con.Open();

                da.Fill(dtCabinet);

                if (dtCabinet.Rows.Count > 0)
                {
                    for (int i = 0; i < dtCabinet.Rows.Count; i++)
                    {
                        DispatchCabinetAuthorityModel m = new DispatchCabinetAuthorityModel();
                        m.CabinetauthorityID = Convert.ToInt32(dtCabinet.Rows[i][0]);
                        m.Referencenumber = dtCabinet.Rows[i][1].ToString();
                        m.Dateentered = Convert.ToDateTime(dtCabinet.Rows[i][2]);
                        m.Collectedby = dtCabinet.Rows[i][3].ToString();
                        m.Enteredby = dtCabinet.Rows[i][4].ToString();
                        m.NamesofOfficials = dtCabinet.Rows[i][5].ToString();
                        m.Destination = dtCabinet.Rows[i][6].ToString();
                        m_Minstries.Add(m); 
                    }
                }
            }
            catch (Exception ex)
            {
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return m_Minstries;
        }

        // 

        public String DeleteDispatchCabinetAuthorities(int _CabinetauthorityID)
        {
            con = new SqlConnection(Strcon);

            cmdDelete = new SqlCommand();

            cmdDelete.CommandType = CommandType.StoredProcedure;

            cmdDelete.CommandText = "qryCabinetauthoriedDeleteOne";

            cmdDelete.Parameters.Add("@CabinetauthorityID", SqlDbType.Int).Value = _CabinetauthorityID;

            cmdDelete.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdDelete.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "Unable to Delete Authority";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        #endregion
        #endregion

        #region Policy File

        public String AddPolicyFile(string _sTitle, string _sVolume, string _sTitle2, byte _sPolicyfileinout,
                                    string _sReference_number, DateTime _dtDate_Opened, DateTime _dtDate_Closed,
                                    DateTime _dtate_entered, string _sEntered_by)
        {
            con = new SqlConnection(Strcon);

            cmdinsert = new SqlCommand();

            cmdinsert.CommandType = CommandType.StoredProcedure;

            cmdinsert.CommandText = "usp_PolicyfileInsertOne";

            cmdinsert.Parameters.Add("@Title", SqlDbType.NVarChar).Value = _sTitle;
            cmdinsert.Parameters.Add("@Title2", SqlDbType.NVarChar).Value = _sTitle2;
            cmdinsert.Parameters.Add("@Policyfileinout", SqlDbType.Bit).Value = _sPolicyfileinout;
            cmdinsert.Parameters.Add("@Reference_number", SqlDbType.NVarChar).Value = _sReference_number;
            cmdinsert.Parameters.Add("@Volume", SqlDbType.NVarChar).Value = _sVolume;
            cmdinsert.Parameters.Add("@Date_Opened", SqlDbType.DateTime).Value = _dtDate_Opened;
            cmdinsert.Parameters.Add("@Date_Closed", SqlDbType.DateTime).Value = _dtDate_Closed;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = _dtate_entered;
            cmdinsert.Parameters.Add("@Entered_by", SqlDbType.NVarChar).Value = _sEntered_by;

            cmdinsert.Connection = con;

            da = new SqlDataAdapter();

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "RECORD NOT ADDED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public DataTable GetPolicyFile(String _sSUBJECT, String _sREFERENCENUMBER)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandText = "usp_SearchPolicyFile";

            if (_sSUBJECT != String.Empty)
            {
                cmdselect.Parameters.Add("@SUBJECT", SqlDbType.VarChar).Value = _sSUBJECT;
            }

            if (_sREFERENCENUMBER != String.Empty)
            {
                cmdselect.Parameters.Add("@Referencenumber", SqlDbType.VarChar).Value = _sREFERENCENUMBER;
            }

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            try
            {
                con.Open();

                da.Fill(dt);
            }
            catch (Exception ex)
            {
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return dt;
        }

        #endregion

        #region Cabinet Minutes
        public String AddCabinetMinutes(CabinetMinutesModule Model)
        {
            con = new SqlConnection(Strcon);

            cmdinsert = new SqlCommand();

            cmdinsert.CommandType = CommandType.StoredProcedure;

            cmdinsert.CommandText = "qryCabinetminutesInsertOne";
            cmdinsert.Parameters.Add("@Date_received", SqlDbType.DateTime).Value = Model.Date_received;
            cmdinsert.Parameters.Add("@Date_of_cabinet_minutes", SqlDbType.DateTime).Value = Model.Date_of_cabinet_minutes;
            cmdinsert.Parameters.Add("@cabinet_minutes_number", SqlDbType.VarChar).Value = Model.cabinet_minutes_number;
            cmdinsert.Parameters.Add("@Subject2", SqlDbType.VarChar).Value = Model.Subject2;
            cmdinsert.Parameters.Add("@Subject_purpose_reading", SqlDbType.VarChar).Value = Model.Subject_purpose_reading;
            cmdinsert.Parameters.Add("@Ministry_ID", SqlDbType.Int).Value = Model.Ministry_ID;
            cmdinsert.Parameters.Add("@originator_s_names", SqlDbType.VarChar).Value = Model.originator_s_names;
            cmdinsert.Parameters.Add("@Approved_unapproved__Yes_No", SqlDbType.Bit).Value = Model.Approved_unapproved__Yes_No;
            cmdinsert.Parameters.Add("@Receieved_by_ID", SqlDbType.Int).Value = Model.Receieved_by_ID;
            cmdinsert.Parameters.Add("@Date_approved_unapproved", SqlDbType.DateTime).Value = Model.Date_approved_unapproved;
            cmdinsert.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = Model.Entered_by;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Date_entered;
            cmdinsert.Parameters.Add("@Delivery", SqlDbType.VarChar).Value = Model.Delivery;
            cmdinsert.Parameters.Add("@Dispatchdate", SqlDbType.DateTime).Value = Model.Dispatchdate;
 
            cmdinsert.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "RECORD NOT ADDED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public String UpdateCabinetMinutes(CabinetMinutesModule Model)
        {
            con = new SqlConnection(Strcon);

            cmdUpdate = new SqlCommand();

            cmdUpdate.CommandType = CommandType.StoredProcedure;

            cmdUpdate.CommandText = "qryCabinetminutesEditOne";
            cmdUpdate.Parameters.Add("@Cabinet_minutes_ID", SqlDbType.Int).Value = Model.Cabinet_minutes_ID;
            cmdUpdate.Parameters.Add("@Date_received", SqlDbType.DateTime).Value = Model.Date_received;
            cmdUpdate.Parameters.Add("@Date_of_cabinet_minutes", SqlDbType.DateTime).Value = Model.Date_of_cabinet_minutes;
            cmdUpdate.Parameters.Add("@cabinet_minutes_number", SqlDbType.VarChar).Value = Model.cabinet_minutes_number;
            cmdUpdate.Parameters.Add("@Subject2", SqlDbType.VarChar).Value = Model.Subject2;
            cmdUpdate.Parameters.Add("@Subject_purpose_reading", SqlDbType.VarChar).Value = Model.Subject_purpose_reading;
            cmdUpdate.Parameters.Add("@Ministry_ID", SqlDbType.Int).Value = Model.Ministry_ID;
            cmdUpdate.Parameters.Add("@originator_s_names", SqlDbType.VarChar).Value = Model.originator_s_names;
            cmdUpdate.Parameters.Add("@Approved_unapproved__Yes_No", SqlDbType.Bit).Value = Model.Approved_unapproved__Yes_No;
            cmdUpdate.Parameters.Add("@Receieved_by_ID", SqlDbType.Int).Value = Model.Receieved_by_ID;
            cmdUpdate.Parameters.Add("@Date_approved_unapproved", SqlDbType.DateTime).Value = Model.Date_approved_unapproved;
            cmdUpdate.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = Model.Entered_by;
            cmdUpdate.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Date_entered;
            cmdUpdate.Parameters.Add("@Delivery", SqlDbType.VarChar).Value = Model.Delivery;
            cmdUpdate.Parameters.Add("@Dispatchdate", SqlDbType.DateTime).Value = Model.Dispatchdate;

            cmdUpdate.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdUpdate.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message; //"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public String DeleteCabinetMinutes(CabinetMinutesModule Model)
        {
            con = new SqlConnection(Strcon);

            cmdDelete = new SqlCommand();

            cmdDelete.CommandType = CommandType.StoredProcedure;

            cmdDelete.CommandText = "qryCabinetsminutesDeleteOne";
            cmdDelete.Parameters.Add("@Cabinet_minutes_ID", SqlDbType.Int).Value = Model.Cabinet_minutes_ID;
            cmdDelete.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdDelete.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        #endregion

        #region Classifier
        public String AddClassifier(ClassifierModel Model)
        {
            con = new SqlConnection(Strcon);

            cmdinsert = new SqlCommand();

            cmdinsert.CommandType = CommandType.StoredProcedure;

            cmdinsert.CommandText = "qryClassiferAddOne";

            cmdinsert.Parameters.Add("@Title", SqlDbType.VarChar).Value = Model.Title;
            cmdinsert.Parameters.Add("@Title1", SqlDbType.NVarChar).Value = Model.Title1;
            cmdinsert.Parameters.Add("@Reference_Number", SqlDbType.NVarChar).Value = Model.Reference_Number;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Dateentered;
            cmdinsert.Parameters.Add("@Entered_by", SqlDbType.NVarChar).Value = Model.Enteredby;
            cmdinsert.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "Unable to Add Classifier";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public String UpdateClassifier(ClassifierModel Model)
        {
            con = new SqlConnection(Strcon);

            cmdUpdate = new SqlCommand();

            cmdUpdate.CommandType = CommandType.StoredProcedure;

            cmdUpdate.CommandText = "qryClassifierEditOne";
            cmdUpdate.Parameters.Add("@ClassifierID", SqlDbType.Int).Value = Model.ClassifierID;
            cmdUpdate.Parameters.Add("@Title", SqlDbType.VarChar).Value = Model.Title;
            cmdUpdate.Parameters.Add("@Title1", SqlDbType.NVarChar).Value = Model.Title1;
            cmdUpdate.Parameters.Add("@Reference_Number", SqlDbType.NVarChar).Value = Model.Reference_Number;
            cmdUpdate.Parameters.Add("@Date_entered", SqlDbType.NVarChar).Value = Model.Dateentered;
            cmdUpdate.Parameters.Add("@Entered_by", SqlDbType.NVarChar).Value = Model.Enteredby;
            cmdUpdate.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdUpdate.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "Unable to Add Classifier";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public String DeleteClassifier(ClassifierModel Model)
        {
            con = new SqlConnection(Strcon);

            cmdDelete = new SqlCommand();
            cmdDelete.CommandType = CommandType.StoredProcedure;
            cmdDelete.CommandText = "SPdelete_Classifier";
            cmdDelete.Parameters.Add("@ClassifierID", SqlDbType.Int).Value = Model.ClassifierID;
            cmdDelete.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdDelete.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "Unable to Add Classifier";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        #endregion

        #region Post
        public String AddPost(string _sPost_shortname, string _sPost_fullname, string _sEntered_by, DateTime _dtDate_entered)
        {
            con = new SqlConnection(Strcon);

            cmdinsert = new SqlCommand();

            cmdinsert.CommandType = CommandType.StoredProcedure;

            cmdinsert.CommandText = "uspPostInsertOne";

            cmdinsert.Parameters.Add("@Post_shortname", SqlDbType.NVarChar).Value = _sPost_shortname;
            cmdinsert.Parameters.Add("@Post_fullname", SqlDbType.NVarChar).Value = _sPost_fullname;
            cmdinsert.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = _sEntered_by;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = _dtDate_entered;

            cmdinsert.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "Policy File RECORD NOT ADDED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        //Static Display
        
        public IList<PostModel> GetPostList()
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandText = "usp_GetPostList";

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dtbringupfiles = new DataTable();

            List<PostModel> m_Minstries = new List<PostModel>();
            try
            {
                da.Fill(dtbringupfiles);

                if (dtbringupfiles.Rows.Count > 0)
                {
                    for (int i = 0; i < dtbringupfiles.Rows.Count; i++)
                    {
                        PostModel m = new PostModel();
                        m.iPostid = Convert.ToInt32(dtbringupfiles.Rows[i][0]);
                        m.sPostname = dtbringupfiles.Rows[i][1].ToString();
                        m_Minstries.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_Minstries;
        }

        public IList<PostModel> GetJobtitlecurrent()
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandText = "usp_GetJobtitlecurrent";

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dtbringupfiles = new DataTable();

            List<PostModel> m_Minstries = new List<PostModel>();
            try
            {
                da.Fill(dtbringupfiles);

                if (dtbringupfiles.Rows.Count > 0)
                {
                    for (int i = 0; i < dtbringupfiles.Rows.Count; i++)
                    {
                        PostModel m = new PostModel();
                        m.iPostid = Convert.ToInt32(dtbringupfiles.Rows[i][0]);
                        m.sPostname = dtbringupfiles.Rows[i][1].ToString();
                        m_Minstries.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_Minstries;
        } 
     
        #endregion

        #region Purpose of Approval
        public String AddPurposeofApproval(string _sApproval_fullname, string _sApproval_shortname, DateTime _dtDate_entered, string _sEntered_by)
        {
            con = new SqlConnection(Strcon);

            cmdinsert = new SqlCommand();

            cmdinsert.CommandType = CommandType.StoredProcedure;

            cmdinsert.CommandText = "qryPurposeofapprovalInsertOne";

            cmdinsert.Parameters.Add("@Approval_fullname", SqlDbType.NVarChar).Value = _sApproval_fullname;
            cmdinsert.Parameters.Add("@Approval_shortname", SqlDbType.NVarChar).Value = _sApproval_shortname;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = _dtDate_entered;
            cmdinsert.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = _sEntered_by;

            cmdinsert.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "Purpose of Aproval NOT ADDED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        #endregion

        #region BringUp Files

        public IList<BringupFilesModel> GetBringupFiles()
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandText = "usp_SelectBringupFiles";

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dtbringupfiles = new DataTable();

            List<BringupFilesModel> m_Minstries = new List<BringupFilesModel>();
            try
            {
                da.Fill(dtbringupfiles);

                if (dtbringupfiles.Rows.Count > 0)
                {
                    for (int i = 0; i < dtbringupfiles.Rows.Count; i++)
                    {
                        BringupFilesModel m = new BringupFilesModel();
                        m.FILESID = Convert.ToInt32(dtbringupfiles.Rows[i][0]);
                        m.DATEENTERED = Convert.ToDateTime(dtbringupfiles.Rows[i][1]);
                        m.DATENEEDED = Convert.ToDateTime(dtbringupfiles.Rows[i][2]);
                        m.DAYSAVAILABLE = Convert.ToInt32(dtbringupfiles.Rows[i][3]);
                        m.REASONFORBRINGUP = dtbringupfiles.Rows[i][4].ToString();
                        m.NEEDEDBY = dtbringupfiles.Rows[i][5].ToString();
                        m.REFERENCENUMBER = dtbringupfiles.Rows[i][6].ToString();
                        m.ENTEREDBY = dtbringupfiles.Rows[i][7].ToString();
                        m_Minstries.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_Minstries;
        }

        public IList<BringupFilesModel> GetFilterBringupFiles(String _sReference)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandText = "usp_GetBringUpfiles";

            if (_sReference != "")
            {
                cmdselect.Parameters.Add("@ReferenceNumber", SqlDbType.VarChar).Value = _sReference;
            }
            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dtbringupfiles = new DataTable();

            List<BringupFilesModel> m_Minstries = new List<BringupFilesModel>();
            try
            {
                da.Fill(dtbringupfiles);

                if (dtbringupfiles.Rows.Count > 0)
                {
                    for (int i = 0; i < dtbringupfiles.Rows.Count; i++)
                    {
                        BringupFilesModel m = new BringupFilesModel();
                        m.FILESID = Convert.ToInt32(dtbringupfiles.Rows[i][0]);
                        m.Datereturned = Convert.ToDateTime(dtbringupfiles.Rows[i][1]);
                        m.DATEENTERED = Convert.ToDateTime(dtbringupfiles.Rows[i][2]);
                        m.DATENEEDED = Convert.ToDateTime(dtbringupfiles.Rows[i][3]);
                        m.DAYSAVAILABLE = Convert.ToInt32(dtbringupfiles.Rows[i][4]);
                        m.REASONFORBRINGUP = dtbringupfiles.Rows[i][5].ToString();
                        m.NEEDEDBY = dtbringupfiles.Rows[i][6].ToString();
                        m.REFERENCENUMBER = dtbringupfiles.Rows[i][7].ToString();
                        m.ENTEREDBY = dtbringupfiles.Rows[i][8].ToString();
                        m_Minstries.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
            }
            finally
            {
                con.Close();
            }
            return m_Minstries;
        }

        public String AddBringupFiles(BringupFilesModel model)
        {
            con = new SqlConnection(Strcon);

            cmdinsert = new SqlCommand();

            cmdinsert.CommandType = CommandType.StoredProcedure;
            cmdinsert.CommandText = "qryBringupInsertOne";
            cmdinsert.Parameters.Add("@Date_returned", SqlDbType.DateTime).Value = model.Datereturned;
            cmdinsert.Parameters.Add("@Date_needed", SqlDbType.DateTime).Value = model.DATENEEDED;
            cmdinsert.Parameters.Add("@Days_available_before_bring_up", SqlDbType.Int).Value = model.DAYSAVAILABLE;
            cmdinsert.Parameters.Add("@Reasons_for_bring_up", SqlDbType.VarChar).Value = model.REASONFORBRINGUP;
            cmdinsert.Parameters.Add("@Needed_by", SqlDbType.VarChar).Value = model.NEEDEDBY;
            cmdinsert.Parameters.Add("@ReferenceNumber", SqlDbType.VarChar).Value = model.REFERENCENUMBER;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = DateTime.Now;
            cmdinsert.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = model.ENTEREDBY;

            cmdinsert.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "RECORD NOT ADDED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        /// <summary>
        /// /
        /// </summary>
        /// <param name="_dtDateentered"></param>
        /// <param name="_dtDate_Returned"></param>
        /// <param name="_dtDate_needed"></param>
        /// <param name="_sDays_available_before_bring_up"></param>
        /// <param name="_sReasons_for_bring_up"></param>
        /// <param name="_sNeeded_by"></param>
        /// <param name="_sReferenceNumber"></param>
        /// <param name="_dtDate_entered"></param>
        /// <param name="_dtEntered_by"></param>
        /// <returns>string</returns>
        public String UpdateBringupFiles(BringupFilesModel model)
        {
            con = new SqlConnection(Strcon);

            cmdUpdate = new SqlCommand();

            cmdUpdate.CommandType = CommandType.StoredProcedure;
            cmdUpdate.CommandText = "usp_UpdateBringupFiles";
            cmdUpdate.Parameters.Add("@Dateentered", SqlDbType.DateTime).Value = model.DATEENTERED;
            cmdUpdate.Parameters.Add("@Datereturned", SqlDbType.DateTime).Value = model.Datereturned;
            cmdUpdate.Parameters.Add("@Dateneeded", SqlDbType.DateTime).Value = model.DATENEEDED;
            cmdUpdate.Parameters.Add("@Daysavailablebeforebringup", SqlDbType.Int).Value = model.DAYSAVAILABLE;
            cmdUpdate.Parameters.Add("@Reasonsforbringup", SqlDbType.VarChar).Value = model.REASONFORBRINGUP;
            cmdUpdate.Parameters.Add("@Neededby", SqlDbType.VarChar).Value = model.NEEDEDBY;
            cmdUpdate.Parameters.Add("@Enteredby", SqlDbType.VarChar).Value = DateTime.Now;
            cmdUpdate.Parameters.Add("@FilesID", SqlDbType.Int).Value = model.FILESID;
            cmdUpdate.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdUpdate.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "The Record was not Updated, Please try again!!!";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public String DeleteBringupFiles(int _iFilesID)
        {
            con = new SqlConnection(Strcon);

            cmdinsert = new SqlCommand();

            cmdinsert.CommandType = CommandType.StoredProcedure;
            cmdinsert.CommandText = "usp_DeleteBringupFiles";
            cmdinsert.Parameters.Add("@FilesID", SqlDbType.Int).Value = _iFilesID;
            cmdinsert.Connection = con;
            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "The Record was not Deleted, Please try again!!!";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        #endregion 

        #region  OutGoing mail

        public String AddOutgoingMail(OutGoingmailModel Model)
        {
            con = new SqlConnection(Strcon);

            cmdinsert = new SqlCommand();

            cmdinsert.CommandType = CommandType.StoredProcedure;

            cmdinsert.CommandText = "qryOutgoingMailInsertOne";
            cmdinsert.Parameters.Add("@Date_of_letter", SqlDbType.DateTime).Value = Model.Date_of_letter;
            cmdinsert.Parameters.Add("@Subjects", SqlDbType.VarChar).Value = Model.Subjects;
            cmdinsert.Parameters.Add("@Subjects1", SqlDbType.VarChar).Value = Model.Subjects1;
            cmdinsert.Parameters.Add("@ReferenceNumber", SqlDbType.VarChar).Value = Model.ReferenceNumber;
            cmdinsert.Parameters.Add("@Written_by_ID", SqlDbType.Int).Value = Model.Written_by_ID;
            cmdinsert.Parameters.Add("@Section_department_ID", SqlDbType.Int).Value = Model.Section_department_ID;
            cmdinsert.Parameters.Add("@Approved_by_ID", SqlDbType.Int).Value = Model.Approved_by_ID;
            cmdinsert.Parameters.Add("@Date_approved", SqlDbType.DateTime).Value = Model.Date_approved;
            cmdinsert.Parameters.Add("@Date_of_dispatch", SqlDbType.DateTime).Value = Model.Date_of_dispatch;
            cmdinsert.Parameters.Add("@Date_of_receipt", SqlDbType.DateTime).Value = Model.Date_of_receipt;
            cmdinsert.Parameters.Add("@Delivery_type_ID", SqlDbType.Int).Value = Model.Delivery_type_ID;
            cmdinsert.Parameters.Add("@Addressee_intended_receiver", SqlDbType.VarChar).Value = Model.Addressee_intended_receiver;
            cmdinsert.Parameters.Add("@InternalExternal_ID", SqlDbType.Int).Value = Model.InternalExternal_ID;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Date_entered;
            cmdinsert.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = Model.Entered_by;

            cmdinsert.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "RECORD NOT ADDED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public String UpdateOutgoingMail(OutGoingmailModel Model)
        {
            con = new SqlConnection(Strcon);

            cmdUpdate = new SqlCommand();

            cmdUpdate.CommandType = CommandType.StoredProcedure;

            cmdUpdate.CommandText = "qryOutgoingMailEditOne";
            cmdUpdate.Parameters.Add("@Outgoing_ID", SqlDbType.Int).Value = Model.Outgoing_ID;
            cmdUpdate.Parameters.Add("@Date_of_letter", SqlDbType.DateTime).Value = Model.Date_of_letter;
            cmdUpdate.Parameters.Add("@Subjects", SqlDbType.VarChar).Value = Model.Subjects;
            cmdUpdate.Parameters.Add("@Subjects1", SqlDbType.VarChar).Value = Model.Subjects1;
            cmdUpdate.Parameters.Add("@ReferenceNumber", SqlDbType.VarChar).Value = Model.ReferenceNumber;
            cmdUpdate.Parameters.Add("@Written_by_ID", SqlDbType.Int).Value = Model.Written_by_ID;
            cmdUpdate.Parameters.Add("@Section_department_ID", SqlDbType.Int).Value = Model.Section_department_ID;
            cmdUpdate.Parameters.Add("@Approved_by_ID", SqlDbType.Int).Value = Model.Approved_by_ID;
            cmdUpdate.Parameters.Add("@Date_approved", SqlDbType.DateTime).Value = Model.Date_approved;
            cmdUpdate.Parameters.Add("@Date_of_dispatch", SqlDbType.DateTime).Value = Model.Date_of_dispatch;
            cmdUpdate.Parameters.Add("@Date_of_receipt", SqlDbType.DateTime).Value = Model.Date_of_receipt;
            cmdUpdate.Parameters.Add("@Delivery_type_ID", SqlDbType.Int).Value = Model.Delivery_type_ID;
            cmdUpdate.Parameters.Add("@Addressee_intended_receiver", SqlDbType.VarChar).Value = Model.Addressee_intended_receiver;
            cmdUpdate.Parameters.Add("@InternalExternal_ID", SqlDbType.VarChar).Value = Model.InternalExternal_ID;
            cmdUpdate.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Date_entered;
            cmdUpdate.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = Model.Entered_by;

            cmdUpdate.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdUpdate.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public String DeleteOutgoingMail(OutGoingmailModel Model)
        {
            con = new SqlConnection(Strcon);

            cmdDelete = new SqlCommand();

            cmdDelete.CommandType = CommandType.StoredProcedure;

            cmdDelete.CommandText = "SPdelete_OutgoingMail";
            cmdDelete.Parameters.Add("@Outgoing_ID", SqlDbType.Int).Value = Model.Outgoing_ID;
            cmdDelete.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdDelete.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "RECORD NOT DELETED, TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public IList<DeliveryModel> GetDeliverytype()
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "sp_GetDeliverytype";

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dtactsbills = new DataTable();

            List<DeliveryModel> m_ActsAndbills = new List<DeliveryModel>();
            try
            {
                con.Open();

                da.Fill(dtactsbills);
                if (dtactsbills.Rows.Count > 0)
                {
                    for (int i = 0; i < dtactsbills.Rows.Count; i++)
                    {
                        DeliveryModel m = new DeliveryModel();
                        m.iDeliverytypeID = Convert.ToInt32(dtactsbills.Rows[i][0]); 
                        m.sDeliveryfullname = dtactsbills.Rows[i][1].ToString();
                        m_ActsAndbills.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_ActsAndbills;
        }

        public IList<SignatoriesModel> GetSignatories()
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "sp_GetSignatories";

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dtactsbills = new DataTable();

            List<SignatoriesModel> m_ActsAndbills = new List<SignatoriesModel>();
            try
            {
                con.Open();

                da.Fill(dtactsbills);
                if (dtactsbills.Rows.Count > 0)
                {
                    for (int i = 0; i < dtactsbills.Rows.Count; i++)
                    {
                        SignatoriesModel m = new SignatoriesModel();
                        m.iSignaturerecordsofficerID = Convert.ToInt32(dtactsbills.Rows[i][0]); 
                        m.sNAME = dtactsbills.Rows[i][1].ToString();
                        m_ActsAndbills.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_ActsAndbills;
        }

        public IList<SectionDepartmentModel> GetSectionDepartment()
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "sp_GetSectionDepartment";

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dtactsbills = new DataTable();

            List<SectionDepartmentModel> m_ActsAndbills = new List<SectionDepartmentModel>();
            try
            {
                con.Open();

                da.Fill(dtactsbills);
                if (dtactsbills.Rows.Count > 0)
                {
                    for (int i = 0; i < dtactsbills.Rows.Count; i++)
                    {
                        SectionDepartmentModel m = new SectionDepartmentModel();
                        m.iDepartmentID = Convert.ToInt32(dtactsbills.Rows[i][0]); 
                        m.sDepartmentname = dtactsbills.Rows[i][1].ToString();
                        m_ActsAndbills.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_ActsAndbills;
        }

        public IList<OutGoingmailModel> GetOutgoingmailbyDate(OutGoingmailModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "sp_GetOutgoingmailbyDate";

            cmdselect.Parameters.Add("@DateofDispatch", SqlDbType.DateTime).Value = model.Date_of_dispatch;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dtactsbills = new DataTable();

            List<OutGoingmailModel> m_ActsAndbills = new List<OutGoingmailModel>();
            try
            {
                con.Open();

                da.Fill(dtactsbills);
                if (dtactsbills.Rows.Count > 0)
                {
                    for (int i = 0; i < dtactsbills.Rows.Count; i++)
                    {
                        OutGoingmailModel m = new OutGoingmailModel();
                        m.Outgoing_ID = Convert.ToInt32(dtactsbills.Rows[i][0]);
                        m.Subjects = dtactsbills.Rows[i][1].ToString();
                        m.Subjects1 = dtactsbills.Rows[i][2].ToString();
                        m.ReferenceNumber = dtactsbills.Rows[i][3].ToString();
                        m.Written_by_ID = Convert.ToInt32(dtactsbills.Rows[i][4]);
                        m.Section_department_ID = Convert.ToInt32(dtactsbills.Rows[i][5]);
                        m.Approved_by_ID = Convert.ToInt32(dtactsbills.Rows[i][6]);
                        m.Date_approved = Convert.ToDateTime(dtactsbills.Rows[i][7]);
                        m.Date_of_dispatch = Convert.ToDateTime(dtactsbills.Rows[i][8]);
                        m.Date_of_receipt = Convert.ToDateTime(dtactsbills.Rows[i][9]);
                        m.Delivery_type_ID = Convert.ToInt32(dtactsbills.Rows[i][10]);
                        m.Addressee_intended_receiver = dtactsbills.Rows[i][11].ToString();
                        m.InternalExternal_ID = dtactsbills.Rows[i][12].ToString();
                        m.Date_entered = Convert.ToDateTime(dtactsbills.Rows[i][13]);
                        m.Entered_by = dtactsbills.Rows[i][14].ToString();
                        m_ActsAndbills.Add(m); 
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_ActsAndbills;
        }

        public IList<OutGoingmailModel> GetOutgoingmailbySubject(OutGoingmailModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "sp_GetOutgoingmailbySubject";

            cmdselect.Parameters.Add("@SUBJECT", SqlDbType.VarChar).Value = model.Subjects;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dtactsbills = new DataTable();

            List<OutGoingmailModel> m_ActsAndbills = new List<OutGoingmailModel>();
            try
            {
                con.Open();

                da.Fill(dtactsbills);
                if (dtactsbills.Rows.Count > 0)
                {
                    for (int i = 0; i < dtactsbills.Rows.Count; i++)
                    {
                        OutGoingmailModel m = new OutGoingmailModel();
                        m.Outgoing_ID = Convert.ToInt32(dtactsbills.Rows[i][0]);
                        m.Subjects = dtactsbills.Rows[i][1].ToString();
                        m.Subjects1 = dtactsbills.Rows[i][2].ToString();
                        m.ReferenceNumber = dtactsbills.Rows[i][3].ToString();
                        m.Written_by_ID = Convert.ToInt32(dtactsbills.Rows[i][4]);
                        m.Section_department_ID = Convert.ToInt32(dtactsbills.Rows[i][5]);
                        m.Approved_by_ID = Convert.ToInt32(dtactsbills.Rows[i][6]);
                        m.Date_approved = Convert.ToDateTime(dtactsbills.Rows[i][7]);
                        m.Date_of_dispatch = Convert.ToDateTime(dtactsbills.Rows[i][8]);
                        m.Date_of_receipt = Convert.ToDateTime(dtactsbills.Rows[i][9]);
                        m.Delivery_type_ID = Convert.ToInt32(dtactsbills.Rows[i][10]);
                        m.Addressee_intended_receiver = dtactsbills.Rows[i][11].ToString();
                        m.InternalExternal_ID = dtactsbills.Rows[i][12].ToString();
                        m.Date_entered = Convert.ToDateTime(dtactsbills.Rows[i][13]);
                        m.Entered_by = dtactsbills.Rows[i][14].ToString();
                        m_ActsAndbills.Add(m); 
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_ActsAndbills;
        }

        public IList<OutGoingmailModel> GetOutgoingmailbyReferencenumber(OutGoingmailModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "sp_GetOutgoingmailbyReferencenumber";

            cmdselect.Parameters.Add("@REFERENCENUMBER", SqlDbType.VarChar).Value = model.ReferenceNumber;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dtactsbills = new DataTable();

            List<OutGoingmailModel> m_ActsAndbills = new List<OutGoingmailModel>();
            try
            {
                con.Open();

                da.Fill(dtactsbills);
                if (dtactsbills.Rows.Count > 0)
                {
                    for (int i = 0; i < dtactsbills.Rows.Count; i++)
                    {
                        OutGoingmailModel m = new OutGoingmailModel();
                        m.Outgoing_ID = Convert.ToInt32(dtactsbills.Rows[i][0]);
                        m.Subjects = dtactsbills.Rows[i][1].ToString();
                        m.Subjects1 = dtactsbills.Rows[i][2].ToString();
                        m.ReferenceNumber = dtactsbills.Rows[i][3].ToString();
                        m.Written_by_ID = Convert.ToInt32(dtactsbills.Rows[i][4]);
                        m.Section_department_ID = Convert.ToInt32(dtactsbills.Rows[i][5]);
                        m.Approved_by_ID = Convert.ToInt32(dtactsbills.Rows[i][6]);
                        m.Date_approved = Convert.ToDateTime(dtactsbills.Rows[i][7]);
                        m.Date_of_dispatch = Convert.ToDateTime(dtactsbills.Rows[i][8]);
                        m.Date_of_receipt = Convert.ToDateTime(dtactsbills.Rows[i][9]);
                        m.Delivery_type_ID = Convert.ToInt32(dtactsbills.Rows[i][10]);
                        m.Addressee_intended_receiver = dtactsbills.Rows[i][11].ToString();
                        m.InternalExternal_ID = dtactsbills.Rows[i][12].ToString();
                        m.Date_entered = Convert.ToDateTime(dtactsbills.Rows[i][13]);
                        m.Entered_by = dtactsbills.Rows[i][14].ToString();
                        m_ActsAndbills.Add(m); 
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_ActsAndbills;
        }
            
        #endregion

        #region  Incomming Mail

        public String AddIncommingMail(IncommingmailModel Model)
        {
            con = new SqlConnection(Strcon);

            cmdinsert = new SqlCommand();

            cmdinsert.CommandType = CommandType.StoredProcedure;

            cmdinsert.CommandText = "qryIncomingmailInsertOne";
            cmdinsert.Parameters.Add("@Ministry_ID", SqlDbType.Int).Value = Model.Ministry_ID;
            cmdinsert.Parameters.Add("@Date_of_receipt_Received", SqlDbType.DateTime).Value = Model.Date_of_receipt_Received;
            cmdinsert.Parameters.Add("@Date_of_letter_Date_inside_letter", SqlDbType.DateTime).Value = Model.Date_of_letter_Date_inside_letter;
            cmdinsert.Parameters.Add("@Date_captured", SqlDbType.DateTime).Value = Model.Date_captured;
            cmdinsert.Parameters.Add("@Subject", SqlDbType.VarChar).Value = Model.Subject;
            cmdinsert.Parameters.Add("@Subject2", SqlDbType.VarChar).Value = Model.Subject2;
            cmdinsert.Parameters.Add("@Recipient_Receiver_ID", SqlDbType.Int).Value = Model.Recipient_Receiver_ID;
            cmdinsert.Parameters.Add("@Section_Department_ID", SqlDbType.Int).Value = Model.Section_Department_ID;
            cmdinsert.Parameters.Add("@Ministry_refnumber", SqlDbType.VarChar).Value = Model.Ministry_refnumber;
            cmdinsert.Parameters.Add("@Reference_number", SqlDbType.VarChar).Value = Model.Reference_number;
            cmdinsert.Parameters.Add("@PrivateIndividual", SqlDbType.VarChar).Value = Model.PrivateIndividual;
            cmdinsert.Parameters.Add("@Refered_to", SqlDbType.Int).Value = Model.Refered_to;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Date_entered;
            cmdinsert.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = Model.sEntereredby;
    
            cmdinsert.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "RECORD NOT ADDED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }
    
        public String UpdateIncommingMail(IncommingmailModel Model)
        {
            con = new SqlConnection(Strcon);

            cmdUpdate = new SqlCommand();

            cmdUpdate.CommandType = CommandType.StoredProcedure;

            cmdUpdate.CommandText = "qryIncomingmailEditOne";
            cmdUpdate.Parameters.Add("@Incoming_mail_ID", SqlDbType.Int).Value = Model.Ministry_ID;
            cmdUpdate.Parameters.Add("@Ministry_ID", SqlDbType.Int).Value = Model.Ministry_ID;
            cmdUpdate.Parameters.Add("@Date_of_receipt_Received", SqlDbType.DateTime).Value = Model.Date_of_receipt_Received;
            cmdUpdate.Parameters.Add("@Date_of_letter_Date_inside_letter", SqlDbType.DateTime).Value = Model.Date_of_letter_Date_inside_letter;
            cmdUpdate.Parameters.Add("@Date_captured", SqlDbType.DateTime).Value = Model.Date_captured;
            cmdUpdate.Parameters.Add("@Subject", SqlDbType.VarChar).Value = Model.Subject;
            cmdUpdate.Parameters.Add("@Subject2", SqlDbType.VarChar).Value = Model.Subject2;
            cmdUpdate.Parameters.Add("@Recipient_Receiver_ID", SqlDbType.Int).Value = Model.Recipient_Receiver_ID;
            cmdUpdate.Parameters.Add("@Section_Department_ID", SqlDbType.Int).Value = Model.Section_Department_ID;
            cmdUpdate.Parameters.Add("@Ministry_refnumber", SqlDbType.VarChar).Value = Model.Ministry_refnumber;
            cmdUpdate.Parameters.Add("@Reference_number", SqlDbType.VarChar).Value = Model.Reference_number;
            cmdUpdate.Parameters.Add("@PrivateIndividual", SqlDbType.VarChar).Value = Model.PrivateIndividual;
            cmdUpdate.Parameters.Add("@Refered_to", SqlDbType.Int).Value = Model.Refered_to;
            cmdUpdate.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Date_entered;
            cmdUpdate.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = Model.sEntereredby;
    
            cmdUpdate.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdUpdate.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public String DeleteIncommingMail(IncommingmailModel Model)
        {
            con = new SqlConnection(Strcon);

            cmdUpdate = new SqlCommand();

            cmdUpdate.CommandType = CommandType.StoredProcedure;

            cmdUpdate.CommandText = "SPdelete_IncomingMail";

            cmdUpdate.Parameters.Add("@Incoming_mail_ID", SqlDbType.Int).Value = Model.Incoming_mail_ID;
       
            cmdUpdate.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdUpdate.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "RECORD NOT DELETED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public IList<IncommingmailModel> GetIncommingMailbyMinistryname(IncommingmailModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "sp_GetIncommingMailbyMinistry";

            cmdselect.Parameters.Add("@Ministryname", SqlDbType.VarChar).Value = model.MinistryName;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<IncommingmailModel> m_List = new List<IncommingmailModel>();
            try
            {
                con.Open();

                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        IncommingmailModel m = new IncommingmailModel();
                        m.Incoming_mail_ID = Convert.ToInt32(dt.Rows[i][0]);
                        m.MinistryName = dt.Rows[i][1].ToString();
                        m.Date_of_receipt_Received = Convert.ToDateTime(dt.Rows[i][2]);
                        m.Date_of_letter_Date_inside_letter = Convert.ToDateTime(dt.Rows[i][3]);
                        m.Date_captured = Convert.ToDateTime(dt.Rows[i][4]);
                        m.Recipient_Receiver_ID = dt.Rows[i][5].ToString();
                        m.Section_Department_ID = dt.Rows[i][6].ToString();
                        m.Ministry_refnumber = dt.Rows[i][7].ToString();
                        m.Reference_number = dt.Rows[i][8].ToString();
                        m.PrivateIndividual = dt.Rows[i][9].ToString();
                        m.Refered_to_str = dt.Rows[i][10].ToString();
                        m.Date_entered = Convert.ToDateTime(dt.Rows[i][11]);
                        m.sEntereredby = dt.Rows[i][12].ToString();
                        m.Subject = dt.Rows[i][13].ToString();
                        m.Subject2 = dt.Rows[i][14].ToString();
                        m_List.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }
       
        public IList<IncommingmailModel> GetIncommingMailbyDateofReceipt(IncommingmailModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "sp_GetIncommingMailbyDateofReceipt";

            cmdselect.Parameters.Add("@Dateofreceipt", SqlDbType.DateTime).Value = model.Date_of_receipt_Received;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<IncommingmailModel> m_List = new List<IncommingmailModel>();
            try
            {
                con.Open();

                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        IncommingmailModel m = new IncommingmailModel();
                        m.Incoming_mail_ID = Convert.ToInt32(dt.Rows[i][0]);
                        m.MinistryName = dt.Rows[i][1].ToString();
                        m.Date_of_receipt_Received = Convert.ToDateTime(dt.Rows[i][2]);
                        m.Date_of_letter_Date_inside_letter = Convert.ToDateTime(dt.Rows[i][3]);
                        m.Date_captured = Convert.ToDateTime(dt.Rows[i][4]);
                        m.Recipient_Receiver_ID = dt.Rows[i][5].ToString();
                        m.Section_Department_ID = dt.Rows[i][6].ToString();
                        m.Ministry_refnumber = dt.Rows[i][7].ToString();
                        m.Reference_number = dt.Rows[i][8].ToString();
                        m.PrivateIndividual = dt.Rows[i][9].ToString();
                        m.Refered_to_str = dt.Rows[i][10].ToString();
                        m.Date_entered = Convert.ToDateTime(dt.Rows[i][11]);
                        m.sEntereredby = dt.Rows[i][12].ToString();
                        m.Subject = dt.Rows[i][13].ToString();
                        m.Subject2 = dt.Rows[i][14].ToString();
                        m_List.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }

        public IList<IncommingmailModel> GetIncommingMailbyDateofSubject(IncommingmailModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "sp_GetIncommingMailbySubject";

            cmdselect.Parameters.Add("@Subjects", SqlDbType.VarChar).Value = model.Subject;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<IncommingmailModel> m_List = new List<IncommingmailModel>();
            try
            {
                con.Open();

                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        IncommingmailModel m = new IncommingmailModel();
                        m.Incoming_mail_ID = Convert.ToInt32(dt.Rows[i][0]);
                        m.MinistryName = dt.Rows[i][1].ToString();
                        m.Date_of_receipt_Received = Convert.ToDateTime(dt.Rows[i][2]);
                        m.Date_of_letter_Date_inside_letter = Convert.ToDateTime(dt.Rows[i][3]);
                        m.Date_captured = Convert.ToDateTime(dt.Rows[i][4]);
                        m.Recipient_Receiver_ID = dt.Rows[i][5].ToString();
                        m.Section_Department_ID = dt.Rows[i][6].ToString();
                        m.Ministry_refnumber = dt.Rows[i][7].ToString();
                        m.Reference_number = dt.Rows[i][8].ToString();
                        m.PrivateIndividual = dt.Rows[i][9].ToString();
                        m.Refered_to_str = dt.Rows[i][10].ToString();
                        m.Date_entered = Convert.ToDateTime(dt.Rows[i][11]);
                        m.sEntereredby = dt.Rows[i][12].ToString();
                        m.Subject = dt.Rows[i][13].ToString();
                        m.Subject2 = dt.Rows[i][14].ToString();
                        m_List.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }

        public IList<IncommingmailModel> GetIncommingMailbyDateofReferencenumber(IncommingmailModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "sp_GetIncommingMailbyReferencenumber";

            cmdselect.Parameters.Add("@Referencenumber", SqlDbType.VarChar).Value = model.Reference_number;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<IncommingmailModel> m_List = new List<IncommingmailModel>();
            try
            {
                con.Open();

                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        IncommingmailModel m = new IncommingmailModel();
                        m.Incoming_mail_ID = Convert.ToInt32(dt.Rows[i][0]);
                        m.MinistryName = dt.Rows[i][1].ToString();
                        m.Date_of_receipt_Received = Convert.ToDateTime(dt.Rows[i][2]);
                        m.Date_of_letter_Date_inside_letter = Convert.ToDateTime(dt.Rows[i][3]);
                        m.Date_captured = Convert.ToDateTime(dt.Rows[i][4]);
                        m.Recipient_Receiver_ID = dt.Rows[i][5].ToString();
                        m.Section_Department_ID = dt.Rows[i][6].ToString();
                        m.Ministry_refnumber = dt.Rows[i][7].ToString();
                        m.Reference_number = dt.Rows[i][8].ToString();
                        m.PrivateIndividual = dt.Rows[i][9].ToString();
                        m.Refered_to_str = dt.Rows[i][10].ToString();
                        m.Date_entered = Convert.ToDateTime(dt.Rows[i][11]);
                        m.sEntereredby = dt.Rows[i][12].ToString();
                        m.Subject = dt.Rows[i][13].ToString();
                        m.Subject2 = dt.Rows[i][14].ToString();
                        m_List.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }

        public IList<IncommingmailModel> GetIncommingMailbyDateofPrivateindivisual(IncommingmailModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "sp_GetIncommingMailbyPrivateindivisual";

            cmdselect.Parameters.Add("@Referencenumber", SqlDbType.VarChar).Value = model.PrivateIndividual;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<IncommingmailModel> m_List = new List<IncommingmailModel>();
            try
            {
                con.Open();

                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        IncommingmailModel m = new IncommingmailModel();
                        m.Incoming_mail_ID = Convert.ToInt32(dt.Rows[i][0]);
                        m.MinistryName = dt.Rows[i][1].ToString();
                        m.Date_of_receipt_Received = Convert.ToDateTime(dt.Rows[i][2]);
                        m.Date_of_letter_Date_inside_letter = Convert.ToDateTime(dt.Rows[i][3]);
                        m.Date_captured = Convert.ToDateTime(dt.Rows[i][4]);
                        m.Recipient_Receiver_ID = dt.Rows[i][5].ToString();
                        m.Section_Department_ID = dt.Rows[i][6].ToString();
                        m.Ministry_refnumber = dt.Rows[i][7].ToString();
                        m.Reference_number = dt.Rows[i][8].ToString();
                        m.PrivateIndividual = dt.Rows[i][9].ToString();
                        m.Refered_to_str = dt.Rows[i][10].ToString();
                        m.Date_entered = Convert.ToDateTime(dt.Rows[i][11]);
                        m.sEntereredby = dt.Rows[i][12].ToString();
                        m.Subject = dt.Rows[i][13].ToString();
                        m.Subject2 = dt.Rows[i][14].ToString();
                        m_List.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }

        public IList<RecepientReceiverModel> GetRecepientReceiver()
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "sp_GetRecepientReceiver";
        
            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<RecepientReceiverModel> m_List = new List<RecepientReceiverModel>();

            try
            {
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        RecepientReceiverModel m = new RecepientReceiverModel();
                        m.RecipientreceiverID = Convert.ToInt32(dt.Rows[i][0]);
                        m.NAME = dt.Rows[i][1].ToString();
                     
                        m_List.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }
         
        #endregion 

        #region Mark out

        public String AddMarkoutcard(MarkoutModel Model)
        {
            con = new SqlConnection(Strcon);

            cmdinsert = new SqlCommand();

            cmdinsert.CommandType = CommandType.StoredProcedure;

            cmdinsert.CommandText = "qryMarkoutcardInsertOne";
            cmdinsert.Parameters.Add("@File_number", SqlDbType.VarChar).Value = Model.sFilenumber;
            cmdinsert.Parameters.Add("@Date_taken", SqlDbType.DateTime).Value = Model.Datetaken;
            cmdinsert.Parameters.Add("@Taken_by_Marked_out_to", SqlDbType.Int).Value = Model.Takenby;
            cmdinsert.Parameters.Add("@Date_returned", SqlDbType.DateTime).Value = Model.Datereturned;
            cmdinsert.Parameters.Add("@Issued_out_by", SqlDbType.VarChar).Value = Model.Issuedoutby;
            cmdinsert.Parameters.Add("@Date_to_be_returned", SqlDbType.DateTime).Value = Model.Datetobereturned;
            cmdinsert.Parameters.Add("@Fileout", SqlDbType.Bit).Value = Model.Fileout;
            cmdinsert.Parameters.Add("@Entered_By", SqlDbType.VarChar).Value = Model.FileoutEnteredBy;
            cmdinsert.Parameters.Add("@Date_Entered", SqlDbType.DateTime).Value = Model.DateEntered;
 
            cmdinsert.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = "RECORD NOT ADDED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public String UpdateMarkoutcard(MarkoutModel Model)
        {
            con = new SqlConnection(Strcon);
            cmdUpdate = new SqlCommand();
            cmdUpdate.CommandType = CommandType.StoredProcedure;
            cmdUpdate.CommandText = "qryMarkoutcardEditOne";
            cmdUpdate.Parameters.Add("@File_ID", SqlDbType.VarChar).Value = Model.FileID;
            cmdUpdate.Parameters.Add("@File_number", SqlDbType.VarChar).Value = Model.sFilenumber;
            cmdUpdate.Parameters.Add("@Date_taken", SqlDbType.DateTime).Value = Model.Datetaken;
            cmdUpdate.Parameters.Add("@Taken_by_Marked_out_to", SqlDbType.Int).Value = Model.Takenby;
            cmdUpdate.Parameters.Add("@Date_returned", SqlDbType.DateTime).Value = Model.Datereturned;
            cmdUpdate.Parameters.Add("@Issued_out_by", SqlDbType.VarChar).Value = Model.Issuedoutby;
            cmdUpdate.Parameters.Add("@Date_to_be_returned", SqlDbType.DateTime).Value = Model.Datetobereturned;
            cmdUpdate.Parameters.Add("@Fileout", SqlDbType.Bit).Value = Model.FileoutEnteredBy;
            cmdUpdate.Parameters.Add("@Entered_By", SqlDbType.VarChar).Value = Model.FileoutEnteredBy;
            cmdUpdate.Parameters.Add("@Date_Entered", SqlDbType.DateTime).Value = Model.DateEntered;
            cmdUpdate.Connection = con;
            string ErrMessage = "";

            try
            {
                con.Open();

                cmdUpdate.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public String DELETEMarkoutcard(MarkoutModel Model)
        {
            con = new SqlConnection(Strcon);
            cmdUpdate = new SqlCommand();
            cmdUpdate.CommandType = CommandType.StoredProcedure;
            cmdUpdate.CommandText = "SPdelete_Markoutcard";
            cmdUpdate.Parameters.Add("@File_ID", SqlDbType.VarChar).Value = Model.FileID;
            cmdUpdate.Connection = con;
            string ErrMessage = "";

            try
            {
                con.Open();

                cmdUpdate.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }
 
        public IList<MarkoutModel> GetmarkoutbyFilenumber(MarkoutModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "usp_GetmarkoutbyFilenumber";

            cmdselect.Parameters.Add("@Filenumber", SqlDbType.VarChar).Value = model.sFilenumber;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<MarkoutModel> m_List = new List<MarkoutModel>();
            try
            {
                con.Open();

                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        MarkoutModel m = new MarkoutModel();
                        m.FileID = Convert.ToInt32(dt.Rows[i][0]);
                        m.sFilenumber = dt.Rows[i][1].ToString();
                        m.Datetaken = Convert.ToDateTime(dt.Rows[i][2]);
                        m.Takenby = dt.Rows[i][3].ToString();
                        m.Datereturned = Convert.ToDateTime(dt.Rows[i][4]);
                        m.Issuedoutby = dt.Rows[i][5].ToString();
                        m.Datetobereturned = Convert.ToDateTime(dt.Rows[i][6]);
                        m.Fileout = Convert.ToByte(dt.Rows[i][7]);
                        m.FileoutEnteredBy = dt.Rows[i][8].ToString();
                        m.DateEntered = Convert.ToDateTime(dt.Rows[i][9]);
                        m_List.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }

        public IList<MarkoutModel> GetmarkoutbyDateTaken(MarkoutModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "usp_GetmarkoutbyDateTaken";

            cmdselect.Parameters.Add("@Datetaken", SqlDbType.VarChar).Value = model.Datetaken;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<MarkoutModel> m_List = new List<MarkoutModel>();
            try
            {
                con.Open();

                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        MarkoutModel m = new MarkoutModel();
                        m.FileID = Convert.ToInt32(dt.Rows[i][0]);
                        m.sFilenumber = dt.Rows[i][1].ToString();
                        m.Datetaken = Convert.ToDateTime(dt.Rows[i][2]);
                        m.Takenby = dt.Rows[i][3].ToString();
                        m.Datereturned = Convert.ToDateTime(dt.Rows[i][4]);
                        m.Issuedoutby = dt.Rows[i][5].ToString();
                        m.Datetobereturned = Convert.ToDateTime(dt.Rows[i][6]);
                        m.Fileout = Convert.ToByte(dt.Rows[i][7]);
                        m.FileoutEnteredBy = dt.Rows[i][8].ToString();
                        m.DateEntered = Convert.ToDateTime(dt.Rows[i][9]);
                        m_List.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }

        public IList<MarkoutModel> GetmarkoutbyTakenby(MarkoutModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "usp_GetmarkoutbyTakenby";

            cmdselect.Parameters.Add("@Takenby", SqlDbType.VarChar).Value = model.Takenby;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<MarkoutModel> m_List = new List<MarkoutModel>();
            try
            {
                con.Open();

                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        MarkoutModel m = new MarkoutModel();
                        m.FileID = Convert.ToInt32(dt.Rows[i][0]);
                        m.sFilenumber = dt.Rows[i][1].ToString();
                        m.Datetaken = Convert.ToDateTime(dt.Rows[i][2]);
                        m.Takenby = dt.Rows[i][3].ToString();
                        m.Datereturned = Convert.ToDateTime(dt.Rows[i][4]);
                        m.Issuedoutby = dt.Rows[i][5].ToString();
                        m.Datetobereturned = Convert.ToDateTime(dt.Rows[i][6]);
                        m.Fileout = Convert.ToByte(dt.Rows[i][7]);
                        m.FileoutEnteredBy = dt.Rows[i][8].ToString();
                        m.DateEntered = Convert.ToDateTime(dt.Rows[i][9]);
                        m_List.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }

        #endregion

        #region Policy File
         
        public String AddPolicyFile(PolicyFileModel Model)
        {
            con = new SqlConnection(Strcon);
            cmdinsert = new SqlCommand();
            cmdinsert.CommandType = CommandType.StoredProcedure;
            cmdinsert.CommandText = "qryPolicyfileInsertOne";
            cmdinsert.Parameters.Add("@Title", SqlDbType.VarChar).Value = Model.sTitle;
            cmdinsert.Parameters.Add("@Title2", SqlDbType.VarChar).Value = Model.Title2;
            cmdinsert.Parameters.Add("@Policyfileinout", SqlDbType.Bit).Value = Model.Policyfileinout;
            cmdinsert.Parameters.Add("@Reference_number", SqlDbType.VarChar).Value = Model.Reference_number;
            cmdinsert.Parameters.Add("@Volume", SqlDbType.VarChar).Value = Model.Volume;
            cmdinsert.Parameters.Add("@Date_Opened", SqlDbType.DateTime).Value = Model.Date_Opened;
            cmdinsert.Parameters.Add("@Date_Closed", SqlDbType.DateTime).Value = Model.Date_Closed;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Dateentered;
            cmdinsert.Parameters.Add("@Entered_By", SqlDbType.VarChar).Value = Model.Entered_By;
            cmdinsert.Connection = con;
            string ErrMessage = ""; 
              
            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public String UpdatePolicyFile(PolicyFileModel Model)
        {
            con = new SqlConnection(Strcon);
            cmdUpdate = new SqlCommand();
            cmdUpdate.CommandType = CommandType.StoredProcedure;
            cmdUpdate.CommandText = "qryPolicyfileEditOne";
            cmdUpdate.Parameters.Add("@File_ID", SqlDbType.VarChar).Value = Model.PolicyID;
            cmdUpdate.Parameters.Add("@Title", SqlDbType.VarChar).Value = Model.sTitle;
            cmdUpdate.Parameters.Add("@Title2", SqlDbType.VarChar).Value = Model.Title2;
            cmdUpdate.Parameters.Add("@Policyfileinout", SqlDbType.Bit).Value = Model.Policyfileinout;
            cmdUpdate.Parameters.Add("@Reference_number", SqlDbType.VarChar).Value = Model.Reference_number;
            cmdUpdate.Parameters.Add("@Volume", SqlDbType.VarChar).Value = Model.Volume;
            cmdUpdate.Parameters.Add("@Date_Opened", SqlDbType.DateTime).Value = Model.Date_Opened;
            cmdUpdate.Parameters.Add("@Date_Closed", SqlDbType.DateTime).Value = Model.Date_Closed;
            cmdUpdate.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Dateentered;
            cmdUpdate.Parameters.Add("@Entered_By", SqlDbType.VarChar).Value = Model.Entered_By;
            cmdUpdate.Connection = con;
            string ErrMessage = "";
              
            try
            {
                con.Open();

                cmdUpdate.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public String DeletePolicyFile(PolicyFileModel Model)
        {
            con = new SqlConnection(Strcon);
            cmdDelete = new SqlCommand();
            cmdDelete.CommandType = CommandType.StoredProcedure;
            cmdDelete.CommandText = "SPdelete_Policyfile";
            cmdDelete.Parameters.Add("@File_ID", SqlDbType.VarChar).Value = Model.PolicyID;
            cmdDelete.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdDelete.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        //Search
        public IList<PolicyFileModel> GetPolicybyTitle(PolicyFileModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "usp_SearchPolicybyTitle";

            cmdselect.Parameters.Add("@Title", SqlDbType.VarChar).Value = model.sTitle;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<PolicyFileModel> m_List = new List<PolicyFileModel>();
            try
            { 
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        PolicyFileModel m = new PolicyFileModel();
                        m.PolicyID = Convert.ToInt32(dt.Rows[i][0]);
                        m.sTitle = dt.Rows[i][1].ToString();
                        m.Title2 = dt.Rows[i][2].ToString();
                        m.Policyfileinout = Convert.ToByte(dt.Rows[i][3]);
                        m.Reference_number = dt.Rows[i][4].ToString();
                        m.Volume = dt.Rows[i][5].ToString();
                        m.Date_Opened = Convert.ToDateTime(dt.Rows[i][6]);
                        m.Date_Closed = Convert.ToDateTime(dt.Rows[i][7]);
                        m.Dateentered = Convert.ToDateTime(dt.Rows[i][8]);    
                        m.Entered_By = dt.Rows[i][9].ToString(); 
                        m_List.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }

        public IList<PolicyFileModel> GetPolicybyReference(PolicyFileModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "usp_SearchPolicybyReference";

            cmdselect.Parameters.Add("@Reference", SqlDbType.VarChar).Value = model.Reference_number;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<PolicyFileModel> m_List = new List<PolicyFileModel>();
            try
            {
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        PolicyFileModel m = new PolicyFileModel();
                        m.PolicyID = Convert.ToInt32(dt.Rows[i][0]);
                        m.sTitle = dt.Rows[i][1].ToString();
                        m.Title2 = dt.Rows[i][2].ToString();
                        m.Policyfileinout = Convert.ToByte(dt.Rows[i][3]);
                        m.Reference_number = dt.Rows[i][4].ToString();
                        m.Volume = dt.Rows[i][5].ToString();
                        m.Date_Opened = Convert.ToDateTime(dt.Rows[i][6]);
                        m.Date_Closed = Convert.ToDateTime(dt.Rows[i][7]);
                        m.Dateentered = Convert.ToDateTime(dt.Rows[i][8]);
                        m.Entered_By = dt.Rows[i][9].ToString();
                        m_List.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }

        #endregion

        #region Memo
 
        public String AddMemo(MemoModel Model)
        {
            con = new SqlConnection(Strcon);
            cmdinsert = new SqlCommand();
            cmdinsert.CommandType = CommandType.StoredProcedure;
            cmdinsert.CommandText = "qryMemoInsertOne";
            cmdinsert.Parameters.Add("@Section_Depart_ID", SqlDbType.Int).Value = Model.Section_Depart_ID;
            cmdinsert.Parameters.Add("@Referencenumber", SqlDbType.VarChar).Value = Model.Referencenumber;
            cmdinsert.Parameters.Add("@Subject", SqlDbType.VarChar).Value = Model.Subject;
            cmdinsert.Parameters.Add("@ToTo", SqlDbType.VarChar).Value = Model.ToTo;
            cmdinsert.Parameters.Add("@Destination", SqlDbType.VarChar).Value = Model.Destination;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.DateEntered;
            cmdinsert.Parameters.Add("@Entered_By", SqlDbType.VarChar).Value = Model.Entered_by;
            cmdinsert.Connection = con;
 
            string ErrMessage = ""; 
              
            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public String UpdateMemo(MemoModel Model)
        {
            con = new SqlConnection(Strcon);
            cmdinsert = new SqlCommand();
            cmdinsert.CommandType = CommandType.StoredProcedure;
            cmdinsert.CommandText = "qryMemoEditOne";
            cmdinsert.Parameters.Add("@Section_Department_ID", SqlDbType.Int).Value = Model.SectionDepartmentID;
            cmdinsert.Parameters.Add("@Section_Depart_ID", SqlDbType.Int).Value = Model.Section_Depart_ID;
            cmdinsert.Parameters.Add("@Referencenumber", SqlDbType.VarChar).Value = Model.Referencenumber;
            cmdinsert.Parameters.Add("@Subject", SqlDbType.VarChar).Value = Model.Subject;
            cmdinsert.Parameters.Add("@ToTo", SqlDbType.VarChar).Value = Model.ToTo;
            cmdinsert.Parameters.Add("@Destination", SqlDbType.VarChar).Value = Model.Destination;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.DateEntered;
            cmdinsert.Parameters.Add("@Entered_By", SqlDbType.VarChar).Value = Model.Entered_by;
            cmdinsert.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }
  
        public String DeleteMemo(MemoModel Model)
        {
            con = new SqlConnection(Strcon);
            cmdDelete = new SqlCommand();
            cmdDelete.CommandType = CommandType.StoredProcedure;
            cmdDelete.CommandText = "qryMemoDeleteOne";
            cmdDelete.Parameters.Add("@Section_Department_ID", SqlDbType.Int).Value = Model.SectionDepartmentID;
            cmdDelete.Connection = con;
             
            string ErrMessage = "";

            try
            {
                con.Open();

                cmdDelete.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        ///Search
        ///

        public IList<MemoModel> GetMemobySectionDepartment(MemoModel model)
        {
            con = new SqlConnection(Strcon);
             
            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "usp_GetMemobySectionDepartment";

            cmdselect.Parameters.Add("@DepartmentName", SqlDbType.VarChar).Value = model.Departmentname;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<MemoModel> m_List = new List<MemoModel>();
            try
            {
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        MemoModel m = new MemoModel();
                        m.SectionDepartmentID = Convert.ToInt32(dt.Rows[i][0]);
                        m.Departmentname = dt.Rows[i][1].ToString();
                        m.Referencenumber = dt.Rows[i][2].ToString();
                        m.Subject = dt.Rows[i][3].ToString();
                        m.ToTo = dt.Rows[i][4].ToString();
                        m.Destination = dt.Rows[i][5].ToString();
                        m.DateEntered = Convert.ToDateTime(dt.Rows[i][6]);      
                        m.Entered_by = dt.Rows[i][7].ToString();
                        m_List.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erroth
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }
 
        public IList<MemoModel> GetMemobyReferencenumber(MemoModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "usp_GetMemobyReferencenumber";

            cmdselect.Parameters.Add("@Referencenumber", SqlDbType.VarChar).Value = model.Referencenumber;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<MemoModel> m_List = new List<MemoModel>();
            try
            {
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        MemoModel m = new MemoModel();
                        m.SectionDepartmentID = Convert.ToInt32(dt.Rows[i][0]);
                        m.Departmentname = dt.Rows[i][1].ToString();
                        m.Referencenumber = dt.Rows[i][2].ToString();
                        m.Subject = dt.Rows[i][3].ToString();
                        m.ToTo = dt.Rows[i][4].ToString();
                        m.Destination = dt.Rows[i][5].ToString();
                        m.DateEntered = Convert.ToDateTime(dt.Rows[i][6]);
                        m.Entered_by = dt.Rows[i][7].ToString();
                        m_List.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }

        public IList<MemoModel> GetMemobySubject(MemoModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "usp_GetMemobySubject";

            cmdselect.Parameters.Add("@Subject", SqlDbType.VarChar).Value = model.Subject;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<MemoModel> m_List = new List<MemoModel>();
            try
            {
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        MemoModel m = new MemoModel();
                        m.SectionDepartmentID = Convert.ToInt32(dt.Rows[i][0]);
                        m.Departmentname = dt.Rows[i][1].ToString();
                        m.Referencenumber = dt.Rows[i][2].ToString();
                        m.Subject = dt.Rows[i][3].ToString();
                        m.ToTo = dt.Rows[i][4].ToString();
                        m.Destination = dt.Rows[i][5].ToString();
                        m.DateEntered = Convert.ToDateTime(dt.Rows[i][6]);
                        m.Entered_by = dt.Rows[i][7].ToString();
                        m_List.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }

        #endregion

        #region File

        public String AddFile(FileModel Model)
        {
            con = new SqlConnection(Strcon);
            cmdinsert = new SqlCommand();
            cmdinsert.CommandType = CommandType.StoredProcedure;
            cmdinsert.CommandText = "qryFilesInsertOne";
            cmdinsert.Parameters.Add("@Date_of_Birth", SqlDbType.DateTime).Value = Model.Date_of_Birth;
            cmdinsert.Parameters.Add("@EC_Number", SqlDbType.VarChar).Value = Model.EC_Number;
            cmdinsert.Parameters.Add("@Post", SqlDbType.Int).Value = Model.Post;
            cmdinsert.Parameters.Add("@Date_of_appointment", SqlDbType.DateTime).Value = Model.Date_of_appoment;
            cmdinsert.Parameters.Add("@Date_closed", SqlDbType.DateTime).Value = Model.Date_closed;
            cmdinsert.Parameters.Add("@Title", SqlDbType.VarChar).Value = Model.Title;
            cmdinsert.Parameters.Add("@Volume", SqlDbType.VarChar).Value = Model.Volume;
            cmdinsert.Parameters.Add("@Station", SqlDbType.Int).Value = Model.Station;
            cmdinsert.Parameters.Add("@Date_opened", SqlDbType.DateTime).Value = Model.Date_opened;
            cmdinsert.Parameters.Add("@Reference_number", SqlDbType.VarChar).Value = Model.Reference_number;
            cmdinsert.Parameters.Add("@Fileout", SqlDbType.Bit).Value = Model.Fileout;
            cmdinsert.Parameters.Add("@Address", SqlDbType.VarChar).Value = Model.Address;
            cmdinsert.Parameters.Add("@Title1", SqlDbType.VarChar).Value = Model.Title1;
            cmdinsert.Parameters.Add("@Initial", SqlDbType.VarChar).Value = Model.Initial;
            cmdinsert.Parameters.Add("@Firstname", SqlDbType.VarChar).Value = Model.Firstname;
            cmdinsert.Parameters.Add("@Surname", SqlDbType.VarChar).Value = Model.Firstname;
            cmdinsert.Parameters.Add("@Cellnumber", SqlDbType.VarChar).Value = Model.Cellnumber;
            cmdinsert.Parameters.Add("@Phonenumber", SqlDbType.VarChar).Value = Model.Phonenumber;
            cmdinsert.Parameters.Add("@Town", SqlDbType.VarChar).Value = Model.Town;
            cmdinsert.Parameters.Add("@City", SqlDbType.VarChar).Value = Model.City;
            cmdinsert.Parameters.Add("@Surbub", SqlDbType.VarChar).Value = Model.Surbub;
            cmdinsert.Parameters.Add("@Street_box", SqlDbType.VarChar).Value = Model.Street_box;
            cmdinsert.Parameters.Add("@Relationship", SqlDbType.VarChar).Value = Model.Relationship;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Date_entered;
            cmdinsert.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = Model.Entered_by;
            cmdinsert.Connection = con;
     
            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }
 
        public String UpdateFile(FileModel Model)
        {
            con = new SqlConnection(Strcon);
            cmdUpdate = new SqlCommand();
            cmdUpdate.CommandType = CommandType.StoredProcedure;
            cmdUpdate.CommandText = "qryFilesEditOne";
            cmdUpdate.Parameters.Add("@Files_ID", SqlDbType.DateTime).Value = Model.File_ID;
            cmdUpdate.Parameters.Add("@Date_of_Birth", SqlDbType.DateTime).Value = Model.Date_of_Birth;
            cmdUpdate.Parameters.Add("@EC_Number", SqlDbType.VarChar).Value = Model.EC_Number;
            cmdUpdate.Parameters.Add("@Post", SqlDbType.Int).Value = Model.Post;
            cmdUpdate.Parameters.Add("@Date_of_appointment", SqlDbType.DateTime).Value = Model.Date_of_appoment;
            cmdUpdate.Parameters.Add("@Date_closed", SqlDbType.DateTime).Value = Model.Date_closed;
            cmdUpdate.Parameters.Add("@Title", SqlDbType.VarChar).Value = Model.Title;
            cmdUpdate.Parameters.Add("@Volume", SqlDbType.VarChar).Value = Model.Volume;
            cmdUpdate.Parameters.Add("@Station", SqlDbType.Int).Value = Model.Station;
            cmdUpdate.Parameters.Add("@Date_opened", SqlDbType.DateTime).Value = Model.Date_opened;
            cmdUpdate.Parameters.Add("@Reference_number", SqlDbType.VarChar).Value = Model.Reference_number;
            cmdUpdate.Parameters.Add("@Fileout", SqlDbType.Bit).Value = Model.Fileout;
            cmdUpdate.Parameters.Add("@Address", SqlDbType.VarChar).Value = Model.Address;
            cmdUpdate.Parameters.Add("@Title1", SqlDbType.VarChar).Value = Model.Title1;
            cmdUpdate.Parameters.Add("@Initial", SqlDbType.VarChar).Value = Model.Initial;
            cmdUpdate.Parameters.Add("@Firstname", SqlDbType.VarChar).Value = Model.Firstname;
            cmdUpdate.Parameters.Add("@Surname", SqlDbType.VarChar).Value = Model.Firstname;
            cmdUpdate.Parameters.Add("@Cellnumber", SqlDbType.VarChar).Value = Model.Cellnumber;
            cmdUpdate.Parameters.Add("@Phonenumber", SqlDbType.VarChar).Value = Model.Phonenumber;
            cmdUpdate.Parameters.Add("@Town", SqlDbType.VarChar).Value = Model.Town;
            cmdUpdate.Parameters.Add("@City", SqlDbType.VarChar).Value = Model.City;
            cmdUpdate.Parameters.Add("@Surbub", SqlDbType.VarChar).Value = Model.Surbub;
            cmdUpdate.Parameters.Add("@Street_box", SqlDbType.VarChar).Value = Model.Street_box;
            cmdUpdate.Parameters.Add("@Relationship", SqlDbType.VarChar).Value = Model.Relationship;
            cmdUpdate.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Date_entered;
            cmdUpdate.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = Model.Entered_by;
            cmdUpdate.Connection = con;

            string ErrMessage = "";
             
            try
            {
                con.Open();

                cmdUpdate.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public String DeleteFile(FileModel Model)
        {
            con = new SqlConnection(Strcon);
            cmdDelete = new SqlCommand();
            cmdDelete.CommandType = CommandType.StoredProcedure;
            cmdDelete.CommandText = "qryFilesEditOne";
            cmdDelete.Parameters.Add("@Files_ID", SqlDbType.DateTime).Value = Model.File_ID;
            cmdDelete.Connection = con;
            string ErrMessage = "";

            try
            {
                con.Open();

                cmdDelete.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        //Search 

        public IList<FileModel> GetFilebyReferencenumber(FileModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "usp_GetFilebyReferencenumber";

            cmdselect.Parameters.Add("@REFERENCENUMBER", SqlDbType.VarChar).Value = model.Reference_number;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<FileModel> m_List = new List<FileModel>();
            try
            {
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        FileModel m = new FileModel();
                        m.File_ID = Convert.ToInt32(dt.Rows[i][0]);
                        m.Date_of_Birth = Convert.ToDateTime(dt.Rows[i][1]);
                        m.EC_Number = dt.Rows[i][2].ToString();
                        m.Post = Convert.ToInt32(dt.Rows[i][3]);
                        m.Date_of_appoment = Convert.ToDateTime(dt.Rows[i][4]);
                        m.Date_closed = Convert.ToDateTime(dt.Rows[i][5]).Date;
                        m.Title = dt.Rows[i][6].ToString();
                        m.Volume = dt.Rows[i][7].ToString();
                        m.Station = Convert.ToInt32(dt.Rows[i][8]);
                        m.Date_opened = Convert.ToDateTime(dt.Rows[i][9]);
                        m.Reference_number = dt.Rows[i][10].ToString();
                        m.Fileout = Convert.ToByte(dt.Rows[i][11]);
                        m.Address = dt.Rows[i][12].ToString();
                        m.Title1 = dt.Rows[i][13].ToString();
                        m.Initial = dt.Rows[i][14].ToString();
                        m.Firstname = dt.Rows[i][15].ToString();
                        m.Surname = dt.Rows[i][16].ToString();
                        m.Cellnumber = dt.Rows[i][17].ToString();
                        m.Phonenumber = dt.Rows[i][18].ToString();
                        m.Town = dt.Rows[i][19].ToString();
                        m.City = dt.Rows[i][20].ToString();
                        m.Surbub = dt.Rows[i][21].ToString();
                        m.Street_box = dt.Rows[i][22].ToString();
                        m.Relationship = dt.Rows[i][23].ToString();
                        m.Date_entered = Convert.ToDateTime(dt.Rows[i][24]).Date;
                        m.Entered_by = dt.Rows[i][25].ToString();
                        m_List.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erroth
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }

        public IList<FileModel> GetFilebyTITLE(FileModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "usp_GetFilebyTitle";

            cmdselect.Parameters.Add("@TITLE", SqlDbType.VarChar).Value = model.Title;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<FileModel> m_List = new List<FileModel>();
            try
            {
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        FileModel m = new FileModel();
                        m.File_ID = Convert.ToInt32(dt.Rows[i][0]);
                        m.Date_of_Birth = Convert.ToDateTime(dt.Rows[i][1]);
                        m.EC_Number = dt.Rows[i][2].ToString();
                        m.Post = Convert.ToInt32(dt.Rows[i][3]);
                        m.Date_of_appoment = Convert.ToDateTime(dt.Rows[i][4]);
                        m.Date_closed = Convert.ToDateTime(dt.Rows[i][5]).Date;
                        m.Title = dt.Rows[i][6].ToString();
                        m.Volume = dt.Rows[i][7].ToString();
                        m.Station = Convert.ToInt32(dt.Rows[i][8]);
                        m.Date_opened = Convert.ToDateTime(dt.Rows[i][9]);
                        m.Reference_number = dt.Rows[i][10].ToString();
                        m.Fileout = Convert.ToByte(dt.Rows[i][11]);
                        m.Address = dt.Rows[i][12].ToString();
                        m.Title1 = dt.Rows[i][13].ToString();
                        m.Initial = dt.Rows[i][14].ToString();
                        m.Firstname = dt.Rows[i][15].ToString();
                        m.Surname = dt.Rows[i][16].ToString();
                        m.Cellnumber = dt.Rows[i][17].ToString();
                        m.Phonenumber = dt.Rows[i][18].ToString();
                        m.Town = dt.Rows[i][19].ToString();
                        m.City = dt.Rows[i][20].ToString();
                        m.Surbub = dt.Rows[i][21].ToString();
                        m.Street_box = dt.Rows[i][22].ToString();
                        m.Relationship = dt.Rows[i][23].ToString();
                        m.Date_entered = Convert.ToDateTime(dt.Rows[i][24]).Date;
                        m.Entered_by = dt.Rows[i][25].ToString();
                        m_List.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erroth
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }

        public IList<FileModel> GetFilebyEcnumber(FileModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "usp_GetFilebyECNUMBER";

            cmdselect.Parameters.Add("@ECNUMBER", SqlDbType.VarChar).Value = model.EC_Number;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<FileModel> m_List = new List<FileModel>();
            try
            {
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        FileModel m = new FileModel();
                        m.File_ID = Convert.ToInt32(dt.Rows[i][0]);
                        m.Date_of_Birth = Convert.ToDateTime(dt.Rows[i][1]);
                        m.EC_Number = dt.Rows[i][2].ToString();
                        m.Post = Convert.ToInt32(dt.Rows[i][3]);
                        m.Date_of_appoment = Convert.ToDateTime(dt.Rows[i][4]);
                        m.Date_closed = Convert.ToDateTime(dt.Rows[i][5]).Date;
                        m.Title = dt.Rows[i][6].ToString();
                        m.Volume = dt.Rows[i][7].ToString();
                        m.Station = Convert.ToInt32(dt.Rows[i][8]);
                        m.Date_opened = Convert.ToDateTime(dt.Rows[i][9]);
                        m.Reference_number = dt.Rows[i][10].ToString();
                        m.Fileout = Convert.ToByte(dt.Rows[i][11]);
                        m.Address = dt.Rows[i][12].ToString();
                        m.Title1 = dt.Rows[i][13].ToString();
                        m.Initial = dt.Rows[i][14].ToString();
                        m.Firstname = dt.Rows[i][15].ToString();
                        m.Surname = dt.Rows[i][16].ToString();
                        m.Cellnumber = dt.Rows[i][17].ToString();
                        m.Phonenumber = dt.Rows[i][18].ToString();
                        m.Town = dt.Rows[i][19].ToString();
                        m.City = dt.Rows[i][20].ToString();
                        m.Surbub = dt.Rows[i][21].ToString();
                        m.Street_box = dt.Rows[i][22].ToString();
                        m.Relationship = dt.Rows[i][23].ToString();
                        m.Date_entered = Convert.ToDateTime(dt.Rows[i][24]).Date;
                        m.Entered_by = dt.Rows[i][25].ToString();
                        m_List.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erroth
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }

        #endregion

        #region Station 
           
        public IList<StationsModel> GetStation()
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandText = "usp_GetStations";

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dtbringupfiles = new DataTable();

            List<StationsModel> m_Minstries = new List<StationsModel>();
            try
            {
                da.Fill(dtbringupfiles);

                if (dtbringupfiles.Rows.Count > 0)
                {
                    for (int i = 0; i < dtbringupfiles.Rows.Count; i++)
                    {
                        StationsModel m = new StationsModel();
                        m.StationID = Convert.ToInt32(dtbringupfiles.Rows[i][0]);
                        m.Stationname = dtbringupfiles.Rows[i][1].ToString();
                        m_Minstries.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_Minstries;
        }

        #endregion

        #region Fileindex

        public String AddFileindex(FileindexModel Model)
        {
            con = new SqlConnection(Strcon);
            cmdinsert = new SqlCommand();
            cmdinsert.CommandType = CommandType.StoredProcedure;
            cmdinsert.CommandText = "qryCabinetminutestypeInsertOne";
            cmdinsert.Parameters.Add("@Title", SqlDbType.VarChar).Value = Model.Title;
            cmdinsert.Parameters.Add("@Title1", SqlDbType.VarChar).Value = Model.Title1;
            cmdinsert.Parameters.Add("@ReferenceNumber", SqlDbType.VarChar).Value = Model.ReferenceNumber;
            cmdinsert.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = Model.Entered_by;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Date_entered;
            cmdinsert.Connection = con;
 
            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public String UpdateFileindex(FileindexModel Model)
        {
            con = new SqlConnection(Strcon);
            cmdinsert = new SqlCommand();
            cmdinsert.CommandType = CommandType.StoredProcedure;
            cmdinsert.CommandText = "qryCabinetminutestypeEditOne";
            cmdinsert.Parameters.Add("@Cabinet_minutes_type_ID", SqlDbType.VarChar).Value = Model.Cabinet_minutes_type_ID;
            cmdinsert.Parameters.Add("@Title", SqlDbType.VarChar).Value = Model.Title;
            cmdinsert.Parameters.Add("@Title1", SqlDbType.VarChar).Value = Model.Title1;
            cmdinsert.Parameters.Add("@ReferenceNumber", SqlDbType.VarChar).Value = Model.ReferenceNumber;
            cmdinsert.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = Model.Entered_by;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Date_entered;
            cmdinsert.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public String DeleteFileindex(FileindexModel Model)
        {
            con = new SqlConnection(Strcon);
            cmdDelete = new SqlCommand();
            cmdDelete.CommandType = CommandType.StoredProcedure;
            cmdDelete.CommandText = "usp_DeleteFileindex";
            cmdDelete.Parameters.Add("@Cabinet_minutes_type_ID", SqlDbType.VarChar).Value = Model.Cabinet_minutes_type_ID;
            cmdDelete.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdDelete.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        public IList<FileindexModel> GetFileindexbyTitle(FileindexModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "usp_GetFileindexbyTitle";

            cmdselect.Parameters.Add("@Title", SqlDbType.VarChar).Value = model.Title;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<FileindexModel> m_List = new List<FileindexModel>();
            try
            {
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        FileindexModel m = new FileindexModel();
                        m.Cabinet_minutes_type_ID = Convert.ToInt32(dt.Rows[i][0]);
                        m.Title = dt.Rows[i][1].ToString();
                        m.Title1 = dt.Rows[i][2].ToString();
                        m.ReferenceNumber = dt.Rows[i][3].ToString();
                        m.Entered_by = dt.Rows[i][4].ToString();
                        m.Date_entered = Convert.ToDateTime(dt.Rows[i][5]);
                        m_List.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erroth
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }

        public IList<FileindexModel> GetFileindexbyReferencenumber(FileindexModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "usp_GetFileindexbyReferencenumber";

            cmdselect.Parameters.Add("@Referencenumber", SqlDbType.VarChar).Value = model.ReferenceNumber;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<FileindexModel> m_List = new List<FileindexModel>();
            try
            {
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        FileindexModel m = new FileindexModel();
                        m.Cabinet_minutes_type_ID = Convert.ToInt32(dt.Rows[i][0]);
                        m.Title = dt.Rows[i][1].ToString();
                        m.Title1 = dt.Rows[i][2].ToString();
                        m.ReferenceNumber = dt.Rows[i][3].ToString();
                        m.Entered_by = dt.Rows[i][4].ToString();
                        m.Date_entered = Convert.ToDateTime(dt.Rows[i][5]);
                        m_List.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erroth
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }

        #endregion

        #region Defence Minutes Main

        #region Defence Minutes Container

             #region Defence Officials


                public string AddDefenceofficials(DefenceMinutesModel Model)
                {
            

                    con = new SqlConnection(Strcon);
                    cmdinsert = new SqlCommand();
                    cmdinsert.CommandType = CommandType.StoredProcedure;
                    cmdinsert.CommandText = "qryDefenceofficialsInsertOne";
                    cmdinsert.Parameters.Add("@Title", SqlDbType.VarChar).Value = Model.Title;
                    cmdinsert.Parameters.Add("@Initial", SqlDbType.VarChar).Value = Model.Initial;
                    cmdinsert.Parameters.Add("@First_name", SqlDbType.VarChar).Value = Model.Firstname;
                    cmdinsert.Parameters.Add("@Surname", SqlDbType.VarChar).Value = Model.Surname;
                    cmdinsert.Parameters.Add("@Middle_name", SqlDbType.VarChar).Value = Model.Middlename;
                    cmdinsert.Parameters.Add("@Ministry_ID", SqlDbType.Int).Value = Model.MinistryID;
                    cmdinsert.Parameters.Add("@Job_title_current", SqlDbType.Int).Value = Model.Jobtitlecurrent;
                    cmdinsert.Parameters.Add("@Defence_minutes_ID", SqlDbType.Int).Value = Model.DefenceMinutesID;
                    cmdinsert.Parameters.Add("@New_title_status", SqlDbType.Int).Value = Model.Newtitlestatus;
                    cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Dateentered;
                    cmdinsert.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = Model.Enteredby;
                       
                    cmdinsert.Connection = con;

                    string ErrMessage = "";

                    try
                    {
                        con.Open();

                        cmdinsert.ExecuteNonQuery();

                        ErrMessage = "";
                    }
                    catch (Exception ex)
                    {
                        ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                        //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
                    }
                    finally
                    {
                        con.Close();
                    }
                    return ErrMessage;
                }


                public string UpdateDefenceofficials(DefenceMinutesModel Model)
                { 
                    con = new SqlConnection(Strcon);
                    cmdUpdate = new SqlCommand();
                    cmdUpdate.CommandType = CommandType.StoredProcedure;
                    cmdUpdate.CommandText = "qryDefenceofficialsInsertOne";
                    cmdinsert.Parameters.Add("@Title", SqlDbType.VarChar).Value = Model.Title;
                    cmdinsert.Parameters.Add("@Initial", SqlDbType.VarChar).Value = Model.Initial;
                    cmdinsert.Parameters.Add("@First_name", SqlDbType.VarChar).Value = Model.Firstname;
                    cmdinsert.Parameters.Add("@Surname", SqlDbType.VarChar).Value = Model.Surname;
                    cmdinsert.Parameters.Add("@Middle_name", SqlDbType.VarChar).Value = Model.Middlename;
                    cmdinsert.Parameters.Add("@Ministry_ID", SqlDbType.Int).Value = Model.MinistryID;
                    cmdinsert.Parameters.Add("@Job_title_current", SqlDbType.Int).Value = Model.Jobtitlecurrent;
                    cmdinsert.Parameters.Add("@Defence_minutes_ID", SqlDbType.Int).Value = Model.DefenceMinutesID;
                    cmdinsert.Parameters.Add("@New_title_status", SqlDbType.Int).Value = Model.Newtitlestatus;
                    cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Dateentered;
                    cmdinsert.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = Model.Enteredby;
                    cmdUpdate.Parameters.Add("@DEFENCEMINUTESOFFICIALNAMEID", SqlDbType.Int).Value = Model.DefenceMinutesofficialID;
 
                    cmdUpdate.Connection = con;
                     
                    string ErrMessage = "";

                    try
                    {
                        con.Open();

                        cmdUpdate.ExecuteNonQuery();

                        ErrMessage = "";
                    }
                    catch (Exception ex)
                    {
                        ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                        //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
                    }
                    finally
                    {
                        con.Close();
                    }
                    return ErrMessage;
                }



                public string DeleteDefenceofficials(DefenceMinutesModel Model)
                {
                    con = new SqlConnection(Strcon);
                    cmdDelete = new SqlCommand();
                    cmdDelete.CommandType = CommandType.StoredProcedure;
                    cmdDelete.CommandText = "usp_DeleteOfficials";
                    cmdDelete.Parameters.Add("@DefenceminutesofficialnameId", SqlDbType.VarChar).Value = Model.DefenceMinutesofficialID;
                    cmdDelete.Connection = con;

                    string ErrMessage = "";

                    try
                    {
                        con.Open();

                        cmdDelete.ExecuteNonQuery();

                        ErrMessage = "";
                    }
                    catch (Exception ex)
                    {
                        ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                        //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
                    }
                    finally
                    {
                        con.Close();
                    }
                    return ErrMessage;
                }


                public IList<DefenceMinutesModel> GetminutesOfficialsname(DefenceMinutesModel model)
                {
                    con = new SqlConnection(Strcon);

                    cmdselect = new SqlCommand();

                    cmdselect.CommandType = CommandType.StoredProcedure;

                    cmdselect.CommandTimeout = 0;

                    cmdselect.CommandText = "usp_GetminutesOfficialsname";

                    cmdselect.Parameters.Add("@DEFENCEMINUTESID", SqlDbType.VarChar).Value = model.DefenceMinutesID;

                    cmdselect.Connection = con;

                    da = new SqlDataAdapter();

                    da.SelectCommand = cmdselect;

                    DataTable dt = new DataTable();

                    List<DefenceMinutesModel> m_List = new List<DefenceMinutesModel>();
                    try
                    {
                        con.Open();

                        da.Fill(dt);

                        if (dt.Rows.Count > 0)
                        {
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                DefenceMinutesModel m = new DefenceMinutesModel();
                                m.DefenceMinutesofficialID = Convert.ToInt32(dt.Rows[i][0]);
                                m.Title = dt.Rows[i][1].ToString();
                                m.Initial = dt.Rows[i][2].ToString();
                                m.Firstname =  dt.Rows[i][3].ToString();
                                m.Surname = dt.Rows[i][4].ToString();
                                m.Middlename =  dt.Rows[i][5].ToString();
                                m.MinistryID = Convert.ToInt32(dt.Rows[i][6]);
                                m.Jobtitlecurrent = Convert.ToInt32(dt.Rows[i][7]);
                                m.DefenceMinutesID = Convert.ToInt32(dt.Rows[i][8]);
                                m.Newtitlestatus =Convert.ToInt32(dt.Rows[i][9]); 
                                m_List.Add(m);
 
                            }
                        }
                    }
                    catch (SqlException ex)
                    {
                        //log the Erroth
                    }
                    finally
                    {
                        con.Close();
                    }
                    return m_List;
                }


        #endregion

             #region Defence Minutes Internal

 

        public string AddDefenceMinutes(DefenceMinutesModel Model)
        {


            con = new SqlConnection(Strcon);
            cmdinsert = new SqlCommand();
            cmdinsert.CommandType = CommandType.StoredProcedure;
            cmdinsert.CommandText = "qryDefenceminutesInsertOne";
            cmdinsert.Parameters.Add("@Purpose_of_approval_ID", SqlDbType.Int).Value = Model.Purposeofapprovalid;
            cmdinsert.Parameters.Add("@Date_of_approval", SqlDbType.DateTime).Value = Model.Dateofapproval;
            cmdinsert.Parameters.Add("@Date_of_unapproval", SqlDbType.DateTime).Value = Model.Dateofunapproval;
            cmdinsert.Parameters.Add("@Approved_yes_no", SqlDbType.Int).Value = Model.Approved;
            cmdinsert.Parameters.Add("@Date_of_defence_minutes", SqlDbType.DateTime).Value = Model.Dateofdefenceminutes;
            cmdinsert.Parameters.Add("@Date_recieved", SqlDbType.DateTime).Value = Model.Daterecevived;
            cmdinsert.Parameters.Add("@Defence_minutes_number", SqlDbType.VarChar).Value = Model.Defenceminutesnumber;
            cmdinsert.Parameters.Add("@Withdram_By", SqlDbType.VarChar).Value = Model.Withdrawnby;
            cmdinsert.Parameters.Add("@Date_withdrawn", SqlDbType.DateTime).Value = Model.Datewithdrwawn;
            cmdinsert.Parameters.Add("@Reasons", SqlDbType.VarChar).Value = Model.Reasons;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Dateentered;
            cmdinsert.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = Model.Enteredby;
            cmdinsert.Parameters.Add("@Subject1", SqlDbType.VarChar).Value = Model.Subject1;
            cmdinsert.Parameters.Add("@Subject2", SqlDbType.VarChar).Value = Model.Subject2; 
            cmdinsert.Connection = con;

 
            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }



        public string UpateDefenceMinutes(DefenceMinutesModel Model)
        {


            con = new SqlConnection(Strcon);
            cmdUpdate = new SqlCommand();
            cmdUpdate.CommandType = CommandType.StoredProcedure;
            cmdUpdate.CommandText = "qryDefenceminutesEditOne";
            cmdUpdate.Parameters.Add("@Purpose_of_approval_ID", SqlDbType.Int).Value = Model.Purposeofapprovalid;
            cmdUpdate.Parameters.Add("@Date_of_approval", SqlDbType.DateTime).Value = Model.Dateofapproval;
            cmdUpdate.Parameters.Add("@Date_of_unapproval", SqlDbType.DateTime).Value = Model.Dateofunapproval;
            cmdUpdate.Parameters.Add("@Approved_yes_no", SqlDbType.Int).Value = Model.Approved;
            cmdUpdate.Parameters.Add("@Date_of_defence_minutes", SqlDbType.DateTime).Value = Model.Dateofdefenceminutes;
            cmdUpdate.Parameters.Add("@Date_recieved", SqlDbType.DateTime).Value = Model.Daterecevived;
            cmdUpdate.Parameters.Add("@Defence_minutes_number", SqlDbType.VarChar).Value = Model.Defenceminutesnumber;
            cmdUpdate.Parameters.Add("@Withdram_By", SqlDbType.VarChar).Value = Model.Withdrawnby;
            cmdUpdate.Parameters.Add("@Date_withdrawn", SqlDbType.DateTime).Value = Model.Datewithdrwawn;
            cmdUpdate.Parameters.Add("@Reasons", SqlDbType.VarChar).Value = Model.Reasons;
            cmdUpdate.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Dateentered;
            cmdUpdate.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = Model.Enteredby;
            cmdUpdate.Parameters.Add("@Subject1", SqlDbType.VarChar).Value = Model.Subject1;
            cmdUpdate.Parameters.Add("@Subject2", SqlDbType.VarChar).Value = Model.Subject2;
            cmdUpdate.Connection = con;
    

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdUpdate.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }

        
        public string DeleteDefenceMinutes(DefenceMinutesModel Model)
        {


            con = new SqlConnection(Strcon);
            cmdDelete = new SqlCommand();
            cmdDelete.CommandType = CommandType.StoredProcedure;
            cmdDelete.CommandText = "usp_DeleteDefenseMinutes";
            cmdDelete.Parameters.Add("@DefenceMinutesID", SqlDbType.Int).Value = Model.DefenceMinutesID;

            cmdDelete.Connection = con;


            string ErrMessage = "";

            try
            {
                con.Open();

                cmdDelete.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }
        
        public IList<DefenceMinutesModel> GetDefenceminutesnumber()
        {
            
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "usp_GetDefenceMinutesnumber";
             
            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<DefenceMinutesModel> m_List = new List<DefenceMinutesModel>();
            try
            {
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DefenceMinutesModel m = new DefenceMinutesModel();
                        m.DefenceMinutesID = Convert.ToInt32(dt.Rows[i][0]);
                        m.Defenceminutesnumber = dt.Rows[i][1].ToString();
                        m_List.Add(m);
                    }
                }
            }
            catch (Exception ex)
            {
                //log the Erroth
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }

        public IList<DefenceMinutesModel> GetPurposeofApproval()
        {

            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "dbo.usp_GetPurposeofApproval";

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<DefenceMinutesModel> m_List = new List<DefenceMinutesModel>();
            try
            {
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DefenceMinutesModel m = new DefenceMinutesModel();
                        m.Purposeofapprovalid = Convert.ToInt32(dt.Rows[i][0]);
                        m.ApprovalFullname = dt.Rows[i][1].ToString(); 
                        m_List.Add(m);
                    }
                }
            }
            catch (Exception ex)
            {
                //log the Erroth
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }
        #endregion
        #endregion

        #region Dispatch
        public string AddDefenceDispatch(DispatchMunutesModel Model)
        {
             

            con = new SqlConnection(Strcon);
            cmdinsert = new SqlCommand();
            cmdinsert.CommandType = CommandType.StoredProcedure;
            cmdinsert.CommandText = "qryDefencedispatchInsertOne";
            cmdinsert.Parameters.Add("@Comptroller_Audotor", SqlDbType.VarChar).Value = Model.Comptroller_Audotor;
            cmdinsert.Parameters.Add("@General_coments", SqlDbType.VarChar).Value = Model.General_coments;
            cmdinsert.Parameters.Add("@DispatchDate", SqlDbType.DateTime).Value = Model.DispatchDate;
            cmdinsert.Parameters.Add("@Ministry_of_Defence__ID", SqlDbType.Int).Value = Model.Ministry_of_Defence__ID;
            cmdinsert.Parameters.Add("@Dispatched_to", SqlDbType.VarChar).Value = Model.Dispatched_to;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Date_entered;
            cmdinsert.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = Model.Entered_by;
            cmdinsert.Connection = con;

            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }


        public string UpdateDefenceDispatch(DispatchMunutesModel Model)
        {
             
            con = new SqlConnection(Strcon);
            cmdinsert = new SqlCommand();
            cmdinsert.CommandType = CommandType.StoredProcedure;
            cmdinsert.CommandText = "qryDefencedispatchEditOne";
            cmdinsert.Parameters.Add("@Defence_minutes_dispatch_ID", SqlDbType.Int).Value = Model.Defence_minutes_dispatch_ID;
            cmdinsert.Parameters.Add("@Comptroller_Audotor", SqlDbType.VarChar).Value = Model.Comptroller_Audotor;
            cmdinsert.Parameters.Add("@General_coments", SqlDbType.VarChar).Value = Model.General_coments;
            cmdinsert.Parameters.Add("@DispatchDate", SqlDbType.DateTime).Value = Model.DispatchDate;
            cmdinsert.Parameters.Add("@Ministry_of_Defence__ID", SqlDbType.Int).Value = Model.Ministry_of_Defence__ID;
            cmdinsert.Parameters.Add("@Dispatched_to", SqlDbType.VarChar).Value = Model.Dispatched_to;
            cmdinsert.Parameters.Add("@Date_entered", SqlDbType.DateTime).Value = Model.Date_entered;
            cmdinsert.Parameters.Add("@Entered_by", SqlDbType.VarChar).Value = Model.Entered_by;
            cmdinsert.Connection = con;
            cmdinsert.Connection = con;


            string ErrMessage = "";

            try
            {
                con.Open();

                cmdinsert.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }


        public string DeleteDefenceDispatch(DispatchMunutesModel Model)
        {


            con = new SqlConnection(Strcon);
            cmdDelete = new SqlCommand();
            cmdDelete.CommandType = CommandType.StoredProcedure;
            cmdDelete.CommandText = "usp_DeleteDefenseMinutesDispatch";
            cmdDelete.Parameters.Add("@DefenceminutesdispatchID", SqlDbType.Int).Value = Model.Defence_minutes_dispatch_ID;
                                      
            cmdDelete.Connection = con;
            
            string ErrMessage = "";

            try
            {
                con.Open();

                cmdDelete.ExecuteNonQuery();

                ErrMessage = "";
            }
            catch (Exception ex)
            {
                ErrMessage = ex.Message;//"RECORD NOT UPDATED TRY AGAIN LATER";
                //LogMessages.Write(ErrorType.Exception, "OrderData.cs", "OrderData", ex.Message, ex.StackTrace, "");
            }
            finally
            {
                con.Close();
            }
            return ErrMessage;
        }
        public IList<DispatchMunutesModel> GetDefenceMinutesDispatch(DispatchMunutesModel model)
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandTimeout = 0;

            cmdselect.CommandText = "sp_GetDefenceMinutesDispatch";

            cmdselect.Parameters.Add("@DefenceMinutesNumber", SqlDbType.VarChar).Value = model.DefenceMinutesNumber;

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dt = new DataTable();

            List<DispatchMunutesModel> m_List = new List<DispatchMunutesModel>();
            try
            {
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DispatchMunutesModel m = new DispatchMunutesModel();
                        m.Defence_minutes_dispatch_ID = Convert.ToInt32(dt.Rows[i][0]);
                        m.Comptroller_Audotor = dt.Rows[i][1].ToString();
                        m.General_coments =  dt.Rows[i][2].ToString();
                        m.DispatchDate = Convert.ToDateTime(dt.Rows[i][3]);
                        m.Dispatched_to = dt.Rows[i][4].ToString();
                        m.Ministry_of_Defence__ID = Convert.ToInt32(dt.Rows[i][5]);
                        m.Date_entered = Convert.ToDateTime(dt.Rows[i][6]);
                        m.Entered_by = dt.Rows[i][7].ToString();
                        m.DefenceMinutesNumber = dt.Rows[i][15].ToString();
                        m_List.Add(m);
 
            
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erroth
            }
            finally
            {
                con.Close();
            }
            return m_List;
        }
       #endregion

        #endregion

        #region Title Status Model 
        public IList<TitleStatusModel> GetJobtitlestatus()
        {
            con = new SqlConnection(Strcon);

            cmdselect = new SqlCommand();

            cmdselect.CommandType = CommandType.StoredProcedure;

            cmdselect.CommandText = "usp_GetJobtitlestatus";

            cmdselect.Connection = con;

            da = new SqlDataAdapter();

            da.SelectCommand = cmdselect;

            DataTable dtbringupfiles = new DataTable();

            List<TitleStatusModel> m_Minstries = new List<TitleStatusModel>();
            try
            {
                da.Fill(dtbringupfiles);

                if (dtbringupfiles.Rows.Count > 0)
                {
                    for (int i = 0; i < dtbringupfiles.Rows.Count; i++)
                    {
                        TitleStatusModel m = new TitleStatusModel();
                        m.StatusID = Convert.ToInt32(dtbringupfiles.Rows[i][0]);
                        m.Statusname = dtbringupfiles.Rows[i][1].ToString();
                        m_Minstries.Add(m);
                    }
                }
            }
            catch (SqlException ex)
            {
                //log the Erro
            }
            finally
            {
                con.Close();
            }
            return m_Minstries;
        }
        
        #endregion

    }
}